﻿Option Explicit On
Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports Utils.LibGlobal

Namespace DBase.MySql


    Public Class DBContext

        Public Property DBStructure As DBStructureBase
        'Private Tables As New Dictionary(Of String, Object)
        Public Property DataUpdate As New Dictionary(Of String, TableDataUpdate)
        Public Property DataInsert As New List(Of TableDataInsert)
        Public Property DataDelete As New List(Of TableDataDelete)
        Public Property DefaultLang As String = "IT"
        'Public Property Cnn As Common.DbConnection
        Public Property CnnString As String
        Public Property DBName As String

        'Public Property TimeStampMin As Date = Utils.Conv.ToTimeStampDate(1)


        Private qMultiInsert As SqlQuery(Of Object)

        Public Function sToBool(v As Boolean) As Integer
            Return DBase.Conv.BoolToDB(v)
        End Function
        Public Function ToDate(v As Object) As Date
            Return DBase.Conv.DateFromDB(v)
        End Function

        Public Sub New(ByVal CnnString As String, DBStructure As DBStructureBase)

            Me.CnnString = CnnString
            Me.DBStructure = DBStructure

        End Sub

        'Public Property CnnString() As String
        '    Get
        '        Return Me.Cnn.ConnectionString
        '    End Get
        '    Set(ByVal value As String)
        '        Me.Cnn = New MySqlConnection(value)
        '    End Set
        'End Property

        Friend _LastID As Integer
        Public ReadOnly Property LastID() As Integer
            Get
                Return _LastID
            End Get
        End Property

        Public Function GetQuery() As DBase.MySql.SqlQuery(Of Object)

            Return New DBase.MySql.SqlQuery(Of Object)(Me)

        End Function
        Public Function GetQuery(Of TResult As {New, Class})() As DBase.MySql.SqlQuery(Of TResult)

            Return New DBase.MySql.SqlQuery(Of TResult)(Me)

        End Function

        'Public Sub AddChangedHandle(e As System.ComponentModel.INotifyPropertyChanged)

        '    AddHandler e.PropertyChanged, AddressOf Me.PropertyChanged

        'End Sub

        'Private Sub PropertyChanged(S As Object, e As System.ComponentModel.PropertyChangedEventArgs)

        '    Me.UpdateOnSubmit(S, e.PropertyName)

        'End Sub

        Public Sub DeleteOnSubmit(Entity As Object)

            'DataDelete.Add(Entity)
            'DataDelete.Add(New DBContext.TableDataDelete With {.DBTableName = "", .Entity = Entity})
            DeleteOnSubmit("", Entity)

        End Sub
        Public Sub DeleteOnSubmit(DBTable As String, Entity As Object)

            'DataDelete.Add(Entity)
            SyncLock DataDelete
                DataDelete.Add(New DBContext.TableDataDelete With {.DBTableName = DBTable, .Entity = Entity})
            End SyncLock

        End Sub
        Public Sub InsertOnSubmit(Entity As Object)

            'DataInsert.Add(Entity)
            'DataInsert.Add(New DBContext.TableDataInsert With {.DBTableName = "", .Entity = Entity})
            InsertOnSubmit("", Entity)

        End Sub
        Public Sub InsertOnSubmit(DBTable As String, Entity As Object)

            'DataInsert.Add(Entity)
            SyncLock DataInsert
                DataInsert.Add(New DBContext.TableDataInsert With {.DBTableName = DBTable, .Entity = Entity})
            End SyncLock

        End Sub
        'Public Sub UpdateOnSubmit(Entity As Object, Field As String)

        '    'Me.AddTableCache(Entity)
        '    'IMPORTANTE per modifiche multithread 
        '    SyncLock DataUpdate

        '        Dim tabname = GetTableName(Entity.GetType)

        '        If Not DataUpdate.ContainsKey(tabname) Then
        '            DataUpdate(tabname) = New TableDataUpdate With {.Record = New Dictionary(Of String, TableDataUpdateRecord), .StructureTableName = tabname}
        '        End If

        '        Dim Rec = DataUpdate(tabname).Record
        '        Dim PksValues = GetPKValues(Entity)
        '        Dim PksValueKey = PksToKey(PksValues)

        '        If Not Rec.ContainsKey(PksValueKey) Then
        '            Rec(PksValueKey) = New TableDataUpdateRecord With {.pkvalues = PksValues, .Fields = New Dictionary(Of String, Object)}
        '        End If

        '        Dim fields = Rec(PksValueKey).Fields

        '        fields(Field) = GetFieldValue(Entity, Field)

        '    End SyncLock

        'End Sub
        Public Sub UpdateOnSubmit(DBTable As String, Entity As Object, Field As String)

            'Me.AddTableCache(Entity)
            'IMPORTANTE per modifiche multithread 
            SyncLock DataUpdate

                Dim StructureTableName = GetTableName(Entity.GetType)
                Dim DBTableName = DBTable
                If DBTableName = "" Then
                    DBTableName = StructureTableName
                End If

                If Not DataUpdate.ContainsKey(DBTableName) Then
                    DataUpdate(DBTableName) = New TableDataUpdate With {.Record = New Dictionary(Of String, TableDataUpdateRecord), .StructureTableName = StructureTableName}
                End If

                Dim Rec = DataUpdate(DBTableName).Record
                Dim PksValues = GetPKValues(Entity)
                Dim PksValueKey = PksToKey(PksValues)

                If Not Rec.ContainsKey(PksValueKey) Then
                    Rec(PksValueKey) = New TableDataUpdateRecord With {.pkvalues = PksValues, .Fields = New Dictionary(Of String, Object)}
                End If

                Dim fields = Rec(PksValueKey).Fields

                fields(Field) = GetFieldValue(Entity, Field)

            End SyncLock

        End Sub
        'Public Sub UpdateOnSubmit(DBTable As String, Entity As Object)

        '    'Me.AddTableCache(Entity)
        '    'IMPORTANTE per modifiche multithread 
        '    SyncLock DataUpdate


        '        Dim DBTableName = DBTable
        '        Dim StructureTableName = GetTableName(Entity.GetType)


        '        If Not DataUpdate.ContainsKey(DBTableName) Then
        '            DataUpdate(DBTableName) = New TableDataUpdate With {.Record = New Dictionary(Of String, TableDataUpdateRecord), .StructureTableName = StructureTableName}
        '        End If

        '        Dim Rec = DataUpdate(DBTableName).Record
        '        Dim PksValues = GetPKValues(Entity) 'GetPKValues(Entity)
        '        Dim PksValueKey = PksToKey(PksValues)

        '        If Not Rec.ContainsKey(PksValueKey) Then
        '            'rec(pks_value_key) = New TableDataUpdate With {.pkvalues = pks_value, .Fields = New Dictionary(Of String, Object)}
        '            Rec(PksValueKey) = New TableDataUpdateRecord With {.pkvalues = PksValues, .Fields = New Dictionary(Of String, Object)}
        '        End If

        '        Dim fields = Rec(PksValueKey).Fields

        '        For Each field In Me.DBStructure(StructureTableName).Fields
        '            fields(field.Key) = GetFieldValue(Entity, field.Key)
        '        Next

        '    End SyncLock

        'End Sub
        Public Sub CopyValue(Source As Object, Dest As Object)

            Dim StructureTableName = GetTableName(Source.GetType)
            For Each field In Me.DBStructure(StructureTableName).Fields
                If DBStructure(StructureTableName).DBGeneratedField <> field.Key Then
                    SetFieldValue(Dest, field.Key, GetFieldValue(Source, field.Key))
                End If
            Next

        End Sub

        Public Sub SubmitChanges()
            Dim q = Me.GetQuery
            Dim DBTableName As String
            Dim StructureTableName As String

            For Each datains In Me.DataInsert

                StructureTableName = GetTableName(datains.Entity.GetType)
                If datains.DBTableName = "" Then
                    DBTableName = StructureTableName
                Else
                    DBTableName = datains.DBTableName
                End If

                q.Reset()
                q.Insert(DBTableName)

                For Each field In Me.DBStructure(StructureTableName).Fields
                    If DBStructure(StructureTableName).DBGeneratedField <> field.Key Then
                        q.InsertValues(field.Key, GetFieldValue(datains.Entity, field.Key))
                    End If
                Next
                q.ExecuteCommand(True)

                'IMPORTANTE metto il vaore dato dal DB nel campo di autoincremento
                If DBStructure(StructureTableName).DBGeneratedField IsNot Nothing Then
                    SetFieldValue(datains.Entity, DBStructure(StructureTableName).DBGeneratedField, Me.LastID)
                End If
                'imposto all'oggetto il collegamento col DB
                q.TabUpdateName = datains.DBTableName
                q.AddChangedHandle(datains.Entity)
                'AddChangedHandle(datains.Entity)

            Next

            For Each dataup In Me.DataUpdate

                For Each rec In dataup.Value.Record
                    q.Reset()
                    q.Update(dataup.Key)
                    Dim ctp = 0
                    For Each pk In rec.Value.pkvalues
                        q.Where(Me.DBStructure(dataup.Value.StructureTableName).PrimaryKeys(ctp), "=", pk)
                        ctp += 1
                    Next
                    For Each f In rec.Value.Fields
                        'q.EditField(f.Key) = f.Value
                        q.UpdateValue(f.Key, f.Value)
                    Next
                    q.ExecuteCommand()
                Next
            Next




            For Each datadel In Me.DataDelete


                StructureTableName = GetTableName(datadel.Entity.GetType)
                If datadel.DBTableName = "" Then
                    DBTableName = StructureTableName
                Else
                    DBTableName = datadel.DBTableName
                End If

                q.Reset()
                q.Delete(DBTableName)
                For Each pk In DBStructure(StructureTableName).PrimaryKeys
                    q.Where(pk, "=", GetFieldValue(datadel.Entity, pk))
                Next
                q.ExecuteCommand()
            Next

            'inserisce la multiinsert
            If qMultiInsert IsNot Nothing Then
                Me.qMultiInsert.ExecuteMultiInsert()
            End If


            Me.ResetChanges()

        End Sub
        Public Sub ResetChanges()
            Me.DataUpdate.Clear()
            Me.DataInsert.Clear()
            Me.DataDelete.Clear()

            If Me.qMultiInsert IsNot Nothing Then
                Me.qMultiInsert.Reset()
                Me.qMultiInsert = Nothing
            End If

        End Sub

        Public Sub InitMultiRowsInsert()

            qMultiInsert = Me.GetQuery


        End Sub
        Public Sub InsertRowOnSubmit(Entity As Object)

            Dim StructureTableName = GetTableName(Entity.GetType)
            If qMultiInsert.CmdText = "" Then
                qMultiInsert.Insert(StructureTableName)
            End If

            Dim p(Me.DBStructure(StructureTableName).Fields.Count - 1)
            Dim ct = 0
            For Each field In Me.DBStructure(StructureTableName).Fields
                If DBStructure(StructureTableName).DBGeneratedField <> field.Key Then
                    p(ct) = GetFieldValue(Entity, field.Key)
                Else
                    p(ct) = DBNull.Value
                End If
                ct += 1
            Next

            'Dim p = (From field In Me.DBStructure(StructureTableName).Fields Select GetFieldValue(Entity, field.Key)).ToArray

            qMultiInsert.InsertMultiRow(p)

        End Sub


        'Public Sub InsertEntity(TableName As String, Entity As Object)
        '    Dim q = Me.GetDBQuery


        '    Dim tabname = GetTableName(Entity.GetType)

        '    q.Reset()
        '    q.Insert(TableName)


        '    For Each field In Me.DBStructure(tabname).Fields
        '        q.InsertValues(field.Key, GetFieldValue(Entity, field.Key))
        '    Next
        '    q.ExecuteCommand()


        'End Sub

        Public Function ExecuteCommand(s As String) As Integer

            Return Me.GetQuery.Sql(s).ExecuteCommand


        End Function

        Public Function UnionQuery(Of tres As {New, Class})(qs As List(Of SqlQuery(Of tres))) As SqlQuery(Of tres)

            'Return Me.GetQuery.Sql(s).ExecuteCommand
            Dim qAllQuery As New SqlQuery(Of tres)
            Dim primo = True

            For Each qSingleQuery In qs

                If primo Then
                    qAllQuery = qSingleQuery
                    primo = False
                Else
                    qAllQuery.Union(qSingleQuery)

                End If

            Next

            Return qAllQuery

        End Function

        Public Sub DropTable(Tab As String)

            Me.ExecuteCommand("DROP TABLE IF EXISTS " & Tab)

        End Sub


        Public Sub TruncateTable(Tab As String)

            Me.ExecuteCommand("TRUNCATE TABLE " & Tab)

        End Sub

        Public Sub TransactionStart()

            'Me.Cnn.Open()
            Me.ExecuteCommand("START TRANSACTION")

        End Sub
        Public Sub TransactionCommit()

            Me.ExecuteCommand("COMMIT")
            'Me.Cnn.Close()

        End Sub
        Public Sub TransactionRollback()

            Me.ExecuteCommand("ROLLBACK")
            'Me.Cnn.Close()

        End Sub


        'q.Sql("COMMIT").ExecuteCommand()
        'Private Function GetTableName(tab As Object) As String

        '    Dim t As Type = tab.GetType

        '    Return t.GetCustomAttributesData(0).NamedArguments(0).TypedValue.Value

        'End Function
        Public Shared Function GetTableName(EntityType As Type) As String

            'Return EntityType.GetCustomAttributesData(0).NamedArguments(0).TypedValue.Value
            Return EntityType.Name

        End Function
        'Friend Sub AddTableCache(EntityType As Type)

        '    'Dim t As Type = tab.GetType
        '    Dim name = GetTableName(EntityType)

        '    If Not TablesCache.ContainsKey(name) Then
        '        SyncLock TablesCache

        '            If Not TablesCache.ContainsKey(name) Then

        '                Dim newtab = New TableProperty

        '                Dim PropList = EntityType.GetProperties.ToList

        '                For Each prop In PropList

        '                    Dim attrsdata = prop.GetCustomAttributesData

        '                    For Each attrdata In attrsdata
        '                        If attrdata.ToString.StartsWith("[System.Data.Linq.Mapping.ColumnAttribute") Then
        '                            Dim Args = attrdata.NamedArguments

        '                            Dim f As New FieldProperty
        '                            Dim fname As String = ""
        '                            For Each arg In Args
        '                                If arg.MemberInfo.Name = "Name" Then
        '                                    fname = arg.TypedValue.Value
        '                                End If
        '                                If arg.MemberInfo.Name = "IsPrimaryKey" Then
        '                                    f.IsPrimaryKey = arg.TypedValue.Value
        '                                End If
        '                                If arg.MemberInfo.Name = "IsDbGenerated" Then
        '                                    f.IsDbGenerated = arg.TypedValue.Value
        '                                End If
        '                                'IsDbGenerated
        '                            Next

        '                            newtab.Fields.Add(fname, f)
        '                            If f.IsPrimaryKey Then
        '                                newtab.PrimaryKeys.Add(fname)
        '                            End If
        '                        End If
        '                    Next

        '                Next

        '                Me.TablesCache.Add(name, newtab)

        '            End If

        '        End SyncLock

        '    End If

        '    'Dim PropList As New List(Of Reflection.PropertyInfo)
        '    'Dim PropTemp As Reflection.PropertyInfo
        '    'PropTemp = t.GetProperty(e.PropertyName, Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance Or Reflection.BindingFlags.IgnoreCase)


        '    'Dim t As Type = S.GetType
        '    'Dim PropList As New List(Of Reflection.PropertyInfo)
        '    'Dim PropTemp As Reflection.PropertyInfo
        '    'PropTemp = t.GetProperty(e.PropertyName, Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance Or Reflection.BindingFlags.IgnoreCase)


        'End Sub

        Private Function PksToKey(pk() As Object) As String
            Dim t As String = ""
            For Each o In pk
                t &= o.ToString & "|"
            Next
            Return t

        End Function
        Private Function GetPKValues(Entity As Object) As Object()
            Dim pkval() As Object
            Dim t As Type = Entity.GetType

            Dim PkFields As List(Of String)

            'If DBTable = "" Then
            PkFields = GetPkFields(Entity.GetType)
            'Else
            'PkFields = Me.DBStructure(DBTable).PrimaryKeys
            'End If

            ReDim pkval(PkFields.Count - 1)

            Dim ct = 0
            For Each pkfield In PkFields
                Dim PropTemp = t.GetProperty(pkfield, Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance Or Reflection.BindingFlags.IgnoreCase)

                pkval(ct) = PropTemp.GetValue(Entity, Nothing)

                ct += 1

            Next

            Return pkval

        End Function
        'Private Function GetPKValues(Entity As Object) As Object()

        '    Return GetPKValues("", Entity)

        'End Function
        'Private Function GetPKValues(Entity As Object) As Object()
        '    Dim pkval() As Object
        '    Dim t As Type = Entity.GetType

        '    Dim PkFields = GetPkFields(Entity.GetType)

        '    ReDim pkval(PkFields.Count - 1)

        '    Dim ct = 0
        '    For Each pkfield In PkFields
        '        Dim PropTemp = t.GetProperty(pkfield, Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance Or Reflection.BindingFlags.IgnoreCase)

        '        pkval(ct) = PropTemp.GetValue(Entity, Nothing)

        '        ct += 1

        '    Next

        '    Return pkval

        'End Function
        Public Function GetPkFields(EntityType As Type) As List(Of String)

            'Me.AddTableCache(EntityType)

            Dim tabname = GetTableName(EntityType)
            Return Me.DBStructure(tabname).PrimaryKeys

        End Function
        Public Function GetFieldValue(Entity As Object, Field As String) As Object
            Dim t As Type = Entity.GetType

            Dim PropTemp = t.GetProperty(Field, Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance Or Reflection.BindingFlags.IgnoreCase)

            Return PropTemp.GetValue(Entity, Nothing)


        End Function
        Public Sub SetFieldValue(Entity As Object, Field As String, Value As Object)
            Dim t As Type = Entity.GetType

            Dim PropTemp = t.GetProperty(Field, Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance Or Reflection.BindingFlags.IgnoreCase)

            If PropTemp.PropertyType = Value.GetType Then
                PropTemp.SetValue(Entity, Value, Nothing)
            Else
                PropTemp.SetValue(Entity, Convert.ChangeType(Value, PropTemp.PropertyType), Nothing)
            End If

        End Sub




        'Public Function GetTable(Of Tresult As {New, Class})() As Tresult

        '    Return DBContext.GetTable(Of Tresult)(Me)

        'End Function
        'Public Shared Function GetTable(Of Tresult As {New, Class})(DB As DBContext) As Tresult
        '    'Dim DB As DBContext = Entity.PropertyChangedEvent.Target
        '    Dim DBTable As Object = New Tresult ' DBContext.GetTable(Of Tresult)(DB)

        '    DBTable.Init(DB)

        '    Return DBTable '.WhereAnd(Conditions)

        'End Function


        Public Function CurrDBLang() As String

            Dim Lang = Utils.GetCurrLang

            If Lang = Me.DefaultLang Then
                Lang = ""
            Else
                Lang = "_" & Lang

            End If

            Return Lang


        End Function


        Public Class TableDataUpdate
            Public Record As Dictionary(Of String, TableDataUpdateRecord)
            Public StructureTableName As String
        End Class

        Public Class TableDataUpdateRecord

            Public pkvalues() As Object
            Public Fields As Dictionary(Of String, Object)

        End Class

        Public Class TableProperty

            Public Fields As New Dictionary(Of String, FieldProperty)
            Public PrimaryKeys As New List(Of String)
            Public DBGeneratedField As String

        End Class

        Public Class FieldProperty

            Public FiledType As Type
            Public IsDbGenerated As Boolean = False
            Public IsPrimaryKey As Boolean = False

        End Class
        Public Class TableDataInsert

            Public DBTableName As String
            Public Entity As Object

        End Class
        Public Class TableDataDelete

            Public DBTableName As String
            Public Entity As Object

        End Class


    End Class

    'Partial Public Class Compose(Of Tresult As {New, Class})
    '    Inherits SqlQuery(Of Tresult)

    '    Public Overridable Sub Init(db As DBContext)
    '        Me.SetDB(db)

    '        Me.NotifyChanged(False)


    '    End Sub

    'End Class

    'Public Class TableQuery2(Of tresult As {New, Class})
    '    Inherits Compose(Of tresult)

    '    Public TbName As String

    '    Public ReadOnly Property TbFName() As String
    '        Get
    '            Return Me.DB.DBName & "." & Me.TbName
    '        End Get
    '    End Property

    '    Public Overrides Sub Init(db As DBContext)
    '        MyBase.Init(db)

    '        Me.NotifyChanged(True)

    '        TbName = db.GetTableName(GetType(tresult))

    '        Me.From(TbFName).Select(TbFName & ".*")

    '    End Sub

    '    Public Function GetByPKs(ParamArray PK() As Object) As tresult

    '        Return Me.ByPKs(PK).ToObj

    '    End Function
    '    Public Function ByPKs(ParamArray PK() As Object) As SqlQuery(Of tresult)
    '        Dim pks = DB.GetPkFields(GetType(tresult))

    '        For ct = 0 To pks.Count - 1
    '            Me.Where(pks(ct), "=", PK(ct))
    '        Next

    '        Return Me

    '    End Function

    '    Public Function GetByPK(PK As Object) As tresult
    '        'Dim pks = DB.GetPkFields(GetType(tresult))

    '        'Return Me.WhereAnd(pks(0) & " =", PK).ToObj
    '        'Me.DB.

    '        Return Me.ByPK(PK).ToObj

    '    End Function
    '    Public Function ByPK(PK As Object) As SqlQuery(Of tresult)
    '        Dim pks = DB.GetPkFields(GetType(tresult))

    '        Return Me.Where(pks(0), "=", PK)

    '        'Me.DB.
    '    End Function
    '    Public Sub InsertOnSubmit(Entity As tresult)

    '        'DB.DataInsert.Add(New DBContext.TableDataInsert With {.DBTableName = TbName, .Entity = Entity})

    '        DB.InsertOnSubmit(TbFName, Entity)

    '    End Sub
    '    Public Sub DeleteOnSubmit(Entity As tresult)

    '        'DB.DataInsert.Add(New DBContext.TableDataInsert With {.DBTableName = TbName, .Entity = Entity})

    '        DB.DeleteOnSubmit(TbFName, Entity)

    '    End Sub

    '    Public Sub SubmitChanges()

    '        DB.SubmitChanges()

    '    End Sub

    '    'Public Sub DeleteByPK(PK As Object)

    '    '    Me.ByPK(PK).Delete.ExecuteCommand()

    '    'End Sub

    'End Class




    Public Class TableQuery(Of tresult As {New, Class})
        Inherits SqlQuery(Of tresult)

        Public Property TabName As String

        Public Sub New(db As DBContext)

            MyBase.New(db)
            Me.TabName = Me.DB.DBName & "." & DBContext.GetTableName(GetType(tresult))
            Init()

        End Sub

        Public Sub New(db As DBContext, TabName As String)

            MyBase.New(db)
            Me.TabName = TabName
            Me.TabUpdateName = TabName
            init()

        End Sub

        Private Sub Init()

            Me.SetDB(DB)
            Me.NotifyChanged(True)
            Me.From(Me.TabName).Select(Me.TabName & ".*")

        End Sub
        Public Function GetByPKs(ParamArray PK() As Object) As tresult

            Return Me.ByPKs(PK).ToObj

        End Function
        Public Function ByPKs(ParamArray PK() As Object) As SqlQuery(Of tresult)
            Dim pks = DB.GetPkFields(GetType(tresult))

            For ct = 0 To pks.Count - 1
                Me.Where(pks(ct), "=", PK(ct))
            Next

            Return Me

        End Function

        Public Function GetByPK(PK As Object) As tresult

            Return Me.ByPK(PK).ToObj

        End Function
        Public Function ByPK(PK As Object) As SqlQuery(Of tresult)
            Dim pks = DB.GetPkFields(GetType(tresult))

            Return Me.Where(pks(0), "=", PK)

            'Me.DB.
        End Function
        Public Sub InsertOnSubmit(Entity As tresult)

            'DB.DataInsert.Add(New DBContext.TableDataInsert With {.DBTableName = TbName, .Entity = Entity})

            DB.InsertOnSubmit(Me.TabName, Entity)

        End Sub
        Public Sub DeleteOnSubmit(Entity As tresult)

            'DB.DataInsert.Add(New DBContext.TableDataInsert With {.DBTableName = TbName, .Entity = Entity})

            DB.DeleteOnSubmit(Me.TabName, Entity)

        End Sub

        Public Sub SubmitChanges()

            DB.SubmitChanges()

        End Sub

        'Public Sub DeleteByPK(PK As Object)

        '    Me.ByPK(PK).Delete.ExecuteCommand()

        'End Sub

    End Class



    Public Enum SqlCommndType
        [SELECT] = 0
        [INSERT]
        [UPDATE]
        [DELETE]
    End Enum

    Public Enum SqlSelectCommndType
        [SELECT] = 0
        [FROM]
        [WHERE]
        [GROUP_BY]
        [HAVING]
        [ORDER_BY]
        [LIMIT]
    End Enum
    Public Enum SqlInsetCommndType
        [INSERT_INTO]
        [INSERT_FIELD]
        [INSERT_VALUES]
    End Enum
    Public Enum SqlUpdateCommndType
        [UPDATE]
        [UPDATE_SET]
        [WHERE]
    End Enum
    Public Enum SqlDeleteCommndType
        [DELETE]
        [FROM]
        [WHERE]
    End Enum
    Public Enum SqlJoinType
        [INNER]
        [LEFT]
        [RIGHT]
        '[OUTER]
    End Enum


    'Public Class MySqlQuery(Of TResult As {New, Class})
    '    Inherits MySqlQuery

    '    Public Sub New(ByVal Db As DBContext)
    '        MyBase.New(Db)

    '    End Sub

    '    Public Function ToList2() As List(Of TResult)

    '        Return MyBase.ToList(Of TResult)()

    '    End Function
    '    Public Overloads Function ToObj() As TResult

    '        Return MyBase.ToObj(Of TResult)()

    '    End Function

    'End Class

    ' da fare
    ' poter accedere ai segmenti sql da esterno GetSelectCommand(tipocomando)
    ' join (q as sqlquery,....) prende in automatico solo la farte del from e forse anche la where
    ' join (par1,par2) usa la using con il nome del campo, questo quando i 2 campi della join sono uguali
    Public Class SqlQuery(Of TResult As {New, Class})

        'Public Property Command As MySqlCommand
        'Public Property Cnn As MySqlConnection
        Public Property CnnString As String
        Public Property Parameters As New List(Of MySqlParameter)
        Public Property DT As DataTable
        Public Property TabUpdateName As String

        'Public DataContext As DataContext
        Public Overridable Property DB As DBContext

        Public Shared SqlCommList()() As String = {
            ({"SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "LIMIT"}),
            ({"INSERT INTO", "", "VALUES"}),
            ({"UPDATE", "SET", "WHERE"}),
            ({"DELETE", "FROM", "WHERE"})
        }

        Private _SqlMultiInsert As New Text.StringBuilder
        Private _SqlParser As New Dictionary(Of Integer, String)
        Private _CmdText As String = ""
        Private _LastCmdText As String = ""
        Private _NotifyPropertyChanged As Boolean = False
        'Private _MergeData As Boolean = False

        Public ReadOnly Property CmdText() As String
            Get
                Return _CmdText
            End Get
        End Property

        Private _SqlType As SqlCommndType = SqlCommndType.SELECT
        Public Property SqlType() As SqlCommndType
            Get
                Return _SqlType
            End Get
            Set(ByVal value As SqlCommndType)
                If _SqlType <> value Then
                    Me._SqlParser.Clear()
                End If
                _SqlType = value
            End Set
        End Property

        Public Sub New()
        End Sub
        'Public Sub New(ByVal cnn As MySqlConnection)

        '    SetCnn(cnn)

        'End Sub
        Public Sub New(ByVal cnnstring As String)

            SetCnn(cnnstring)

        End Sub
        Public Sub New(ByVal Db As DBContext)

            SetDB(Db)

        End Sub
        Protected Sub SetDB(ByVal Db As DBContext)

            SetCnn(Db.CnnString)
            Me.DB = Db

        End Sub
        Protected Sub SetCnn(ByVal cnnstring As String)

            Me.CnnString = cnnstring
            Me._CmdText = ""

        End Sub
        'Protected Sub SetCnn(ByVal cnn As MySqlConnection)

        '    Me.Command = New MySqlCommand()
        '    Me.Cnn = DirectCast(cnn, MySqlConnection)
        '    Me.Command.Connection = cnn
        '    Me.Command.CommandTimeout = 120
        '    'Me.RecNum = Integer.MinValue
        '    'Me.RecOff = Integer.MinValue
        '    Me._CmdText = ""

        'End Sub


        'Public Sub New(ByVal DC As DataContext)

        '    Me.New(DC.Connection)
        '    Me.DataContext = DC

        'End Sub

        'Public Function P(ByVal Param As Object) As String
        '    Dim TextParam As String = ""

        '    If Param Is Nothing Then
        '        Param = ""
        '    End If

        '    If Param.GetType = GetType(Date) Then
        '        If Param = Date.MinValue Then
        '            Param = "0000-00-00 00:00:00"
        '            'isnull = True
        '        End If
        '    End If


        '    If Param.GetType.GetInterface("IList") IsNot Nothing Then
        '        TextParam = DBase.Conv.ListToDB(Param)
        '    Else
        '        TextParam = "?" & (Me.Command.Parameters.Count).ToString
        '        Me.Command.Parameters.AddWithValue(TextParam, Param)
        '    End If

        '    Return TextParam & " "
        'End Function
        Public Function P(ByVal Param As Object) As String
            Dim TextParam As String = ""

            If Param Is Nothing Then
                Param = ""
            End If

            If Param.GetType = GetType(Date) Then
                If Param = Date.MinValue Then
                    Param = "0000-00-00 00:00:00"
                    'isnull = True
                End If
            End If


            If Param.GetType.GetInterface("IList") IsNot Nothing Then
                TextParam = DBase.Conv.ListToDB(Param)
            Else
                TextParam = "?" & (Parameters.Count).ToString
                Parameters.Add(New MySqlParameter(TextParam, Param))
            End If

            Return TextParam & " "
        End Function
        Public Function AddSqlQuery(q As Object) As String
            Dim TextSql As String = q.CmdText & " "
            Dim TextParam As String
            Dim params = q.Parameters

            TextSql = TextSql.Replace("?", "#")

            For ct = 0 To params.Count - 1
                Dim Param As MySqlParameter = params(ct)

                If Param.GetType.GetInterface("IList") IsNot Nothing Then
                    TextParam = DBase.Conv.ListToDB(Param.Value)
                Else
                    TextParam = "?" & (Parameters.Count).ToString
                    'TextSql = TextSql.Replace("?" & ct & " ", TextParam & " ")
                    TextSql = TextSql.Replace("#" & ct & " ", TextParam & " ")
                    Parameters.Add(New MySqlParameter(TextParam, Param.Value))
                    'Me.Command.Parameters.AddWithValue(TextParam, Param.Value)
                End If

            Next

            Return TextSql

        End Function

        'Public Function AddSqlQuery(q As Object) As String
        '    Dim TextSql As String = q.CmdText & " "
        '    Dim TextParam As String
        '    Dim params As MySqlParameterCollection = q.Command.Parameters

        '    TextSql = TextSql.Replace("?", "#")

        '    For ct = 0 To params.Count - 1
        '        Dim Param As MySqlParameter = params(ct)

        '        If Param.GetType.GetInterface("IList") IsNot Nothing Then
        '            TextParam = DBase.Conv.ListToDB(Param.Value)
        '        Else
        '            TextParam = "?" & (Me.Command.Parameters.Count).ToString
        '            'TextSql = TextSql.Replace("?" & ct & " ", TextParam & " ")
        '            TextSql = TextSql.Replace("#" & ct & " ", TextParam & " ")
        '            Me.Command.Parameters.AddWithValue(TextParam, Param.Value)
        '        End If

        '    Next

        '    Return TextSql

        'End Function


        Public Function Params(p As Dictionary(Of String, Object)) As String

            If p Is Nothing Then
                Return ""
            End If

            Return Me.Params("and", DictionaryToArray(p))
            'Dim SqlWhere As New Text.StringBuilder

            'For Each Par In params
            '    If SqlWhere.Length <> 0 Then
            '        SqlWhere.Append(" And ")
            '    End If
            '    SqlWhere.Append(Par.Key)
            '    SqlWhere.Append(Me.P(Par.Value))
            'Next
            'SqlWhere.Append(" ")

            'Return SqlWhere.ToString

        End Function
        'Public Function Params(Op As String, ParamArray p() As Object) As String

        '    If p Is Nothing Then
        '        Return ""
        '    End If
        '    Dim SqlWhere As New Text.StringBuilder

        '    For ct As Integer = 0 To p.Count - 1
        '        If ct Mod 3 = 0 Then
        '            If SqlWhere.Length <> 0 Then
        '                SqlWhere.Append(" " & Op & " ")
        '            End If
        '        End If
        '        If ct Mod 3 = 2 Then
        '            SqlWhere.Append(Me.P(p(ct)))
        '        Else
        '            SqlWhere.Append(" " & p(ct) & " ")
        '        End If


        '    Next
        '    SqlWhere.Insert(0, " (")
        '    SqlWhere.Append(") ")

        '    Return SqlWhere.ToString

        'End Function

        Public Function Params(Op As String, ParamArray p() As Object) As String

            If p Is Nothing Then
                Return ""
            End If
            Dim SqlWhere As New Text.StringBuilder

            Dim CtParValue As Integer = 1

            For ct As Integer = 0 To p.Count - 1
                Dim t = p(ct).GetType

                If t.Name = "SqlQuery`1" Then

                    If SqlWhere.Length <> 0 Then
                        SqlWhere.Append(" " & Op & " ")
                    End If

                    'GetCommand(t As SqlSelectCommndType)
                    Dim q As New SqlQuery(Of Object)
                    q.Sql(Me.AddSqlQuery(p(ct)))
                    SqlWhere.Append(" " & q.GetCommand(SqlSelectCommndType.WHERE) & " ")

                    CtParValue = 0
                Else

                    If CtParValue = 1 Then
                        If SqlWhere.Length <> 0 Then
                            SqlWhere.Append(" " & Op & " ")
                        End If
                    End If

                    If CtParValue = 3 Then
                        SqlWhere.Append(Me.P(p(ct)))
                        CtParValue = 0
                    Else
                        SqlWhere.Append(" " & p(ct) & " ")
                    End If

                End If

                CtParValue += 1

            Next
            SqlWhere.Insert(0, " (")
            SqlWhere.Append(") ")

            Return SqlWhere.ToString

        End Function
        Public Function ParamsUpdate(ParamArray p() As Object) As String

            If p Is Nothing Then
                Return ""
            End If
            Dim SqlSetUpdate As New Text.StringBuilder

            For ct As Integer = 0 To p.Count - 1
                If ct Mod 2 = 0 Then
                    If SqlSetUpdate.Length <> 0 Then
                        SqlSetUpdate.Append(" , ")
                    End If
                    SqlSetUpdate.Append(p(ct))
                    SqlSetUpdate.Append(" = ")

                    'If p(ct + 1) IsNot Nothing Then
                    '    If p(ct + 1).GetType = GetType(Date) Then
                    '        If p(ct + 1) = Date.MinValue Then
                    '            p(ct + 1) = "0000-00-00 00:00:00"
                    '            'isnull = True
                    '        End If
                    '    End If
                    'End If

                    SqlSetUpdate.Append(Me.P(p(ct + 1)))
                End If
            Next

            Return SqlSetUpdate.ToString

        End Function

        'Public Function SetSql(s As String) As SqlQuery(Of TResult)

        '    Me.Reset()

        '    Me._CmdText = s

        '    Return Me

        'End Function
        'Public Function Sql(s As String) As SqlQuery(Of TResult)

        '    Dim OldWhere As String = ""
        '    If Me._SqlParser.ContainsKey(SqlSelectCommndType.WHERE) Then
        '        OldWhere = Me._SqlParser(SqlSelectCommndType.WHERE)
        '    End If

        '    Me._CmdText = s

        '    If OldWhere <> "" Then
        '        Me._SqlParser.Clear()
        '        Me.Parse()
        '        Me.WhereAnd(OldWhere)
        '    End If

        '    Return Me

        'End Function
        Public Function Sql(s As String) As SqlQuery(Of TResult)

            Me._SqlParser.Clear()
            Me._CmdText = s.Replace(vbCr, " ").Replace(vbLf, " ")
            Return Me

        End Function

        Public Function [Select](ParamArray Fields() As String) As SqlQuery(Of TResult)

            Me.SqlType = SqlCommndType.SELECT
            Parse()

            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.SELECT) Then
                Me._SqlParser.Add(SqlSelectCommndType.SELECT, "")
            Else
                If Me._SqlParser(SqlSelectCommndType.SELECT) = "*" Then
                    Me._SqlParser(SqlSelectCommndType.SELECT) = ""
                Else
                    Me._SqlParser(SqlSelectCommndType.SELECT) &= " , "
                End If

            End If

            For ct = 0 To Fields.Count - 1
                Me._SqlParser(SqlSelectCommndType.SELECT) &= " " & Fields(ct) & " "
                If ct <> Fields.Count - 1 Then
                    Me._SqlParser(SqlSelectCommndType.SELECT) &= " , "
                End If
            Next


            Me._CmdText = CreateSql()
            Return Me

        End Function
        Public Function SelectLang(ParamArray Fields() As String) As SqlQuery(Of TResult)

            Dim CurrLang = Me.DB.CurrDBLang

            Dim SelLang As String = ""
            For ct = 0 To Fields.Count - 1
                Dim f = Fields(ct).Split({"as"}, StringSplitOptions.RemoveEmptyEntries)
                'Dim FieldName = f(0) & CurrLang

                SelLang &= " " & Trim(f(0)) & CurrLang
                If f.Length > 1 Then
                    SelLang &= " as " & f(1)
                Else
                    SelLang &= " as " & f(0)
                End If

                'SelLang &= " " & Fields(ct) & CurrLang & " as " & Fields(ct)
                If ct <> Fields.Count - 1 Then
                    SelLang &= " , "
                End If
            Next

            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.SELECT) Then
                Me._SqlParser.Add(SqlSelectCommndType.SELECT, SelLang)
            Else
                Me._SqlParser(SqlSelectCommndType.SELECT) = SelLang & " , " & Me._SqlParser(SqlSelectCommndType.SELECT)

            End If

            Me._CmdText = CreateSql()
            Return Me

        End Function


        Public Function From(Table As String) As SqlQuery(Of TResult)

            Return Me.From(Table, "", "")

        End Function
        Public Function From(q As Object, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Dim sqltext = Me.AddSqlQuery(q)

            Return Me.From(" (" & sqltext & ") ", TableAlias, Index)

        End Function

        Public Function From(Table As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Parse()

            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.FROM) Then
                Me._SqlParser.Add(SqlSelectCommndType.FROM, "")
            Else
                Me._SqlParser(SqlSelectCommndType.FROM) &= ""
            End If

            Me._SqlParser(SqlSelectCommndType.FROM) &= " " & Table & " "

            If TableAlias <> "" Then
                Me._SqlParser(SqlSelectCommndType.FROM) &= " as " & TableAlias & " "
            End If

            If Index <> "" Then
                Me._SqlParser(SqlSelectCommndType.FROM) &= " " & Index & " "
            End If

            Me._CmdText = CreateSql()
            Return Me


        End Function

        Public Function Union(q As Object) As SqlQuery(Of TResult)

            Dim sqltext = Me.AddSqlQuery(q)

            Me._CmdText &= " UNION " & sqltext

            Return Me

        End Function
        ' JOIN LEFT
        Public Function JoinLeft(Table As String, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, QueryFiled, "", "", SqlJoinType.LEFT)

        End Function
        Public Function JoinLeft(Table As String, Tablefield As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, Tablefield, "", "", SqlJoinType.LEFT)

        End Function
        Public Function JoinLeft(Table As String, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.LEFT)

        End Function
        Public Function JoinLeft(q As Object, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

            'Return Me.Join(q.GetCommand(SqlSelectCommndType.FROM), Tablefield, QueryFiled, "", "")

            'per aggiungere la where bisogna aggiungere anche i parametri
            Return Me.Join(q, Tablefield, QueryFiled, "", "", SqlJoinType.LEFT)

        End Function
        Public Function JoinLeft(q As Object, Tablefield As String) As SqlQuery(Of TResult)

            'per aggiungere la where bisogna aggiungere anche i parametri
            Return Me.Join(q, Tablefield, Tablefield, "", "", SqlJoinType.LEFT)

        End Function
        Public Function JoinLeft(q As Object, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Return Me.Join(q, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.LEFT)

        End Function

        ' JOIN RIGHT
        Public Function JoinRight(Table As String, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, QueryFiled, "", "", SqlJoinType.RIGHT)

        End Function
        Public Function JoinRight(Table As String, Tablefield As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, Tablefield, "", "", SqlJoinType.RIGHT)

        End Function
        Public Function JoinRight(Table As String, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.RIGHT)

        End Function
        Public Function JoinRight(q As Object, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

            Return Me.Join(q, Tablefield, QueryFiled, "", "", SqlJoinType.RIGHT)

        End Function
        Public Function JoinRight(q As Object, Tablefield As String) As SqlQuery(Of TResult)

            'per aggiungere la where bisogna aggiungere anche i parametri
            Return Me.Join(q, Tablefield, Tablefield, "", "", SqlJoinType.RIGHT)

        End Function
        Public Function JoinRight(q As Object, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Return Me.Join(q, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.RIGHT)

        End Function

        ' JOIN OUTER
        'Public Function JoinOuter(Table As String, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

        '    Return Me.Join(Table, Tablefield, QueryFiled, "", "", SqlJoinType.OUTER)

        'End Function
        'Public Function JoinOuter(Table As String, Tablefield As String) As SqlQuery(Of TResult)

        '    Return Me.Join(Table, Tablefield, Tablefield, "", "", SqlJoinType.OUTER)

        'End Function
        'Public Function JoinOuter(Table As String, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

        '    Return Me.Join(Table, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.OUTER)

        'End Function
        'Public Function JoinOuter(q As Object, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

        '    'Return Me.Join(q.GetCommand(SqlSelectCommndType.FROM), Tablefield, QueryFiled, "", "")

        '    'per aggiungere la where bisogna aggiungere anche i parametri
        '    Return Me.Join(q, Tablefield, QueryFiled, "", "", SqlJoinType.OUTER)

        'End Function
        'Public Function JoinOuter(q As Object, Tablefield As String) As SqlQuery(Of TResult)

        '    'per aggiungere la where bisogna aggiungere anche i parametri
        '    Return Me.Join(q, Tablefield, Tablefield, "", "", SqlJoinType.OUTER)

        'End Function
        'Public Function JoinOuter(q As Object, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

        '    Return Me.Join(q, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.OUTER)

        'End Function


        'Public Function JoinLeft(Table As String, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

        '    Return Me.JoinLeft(Table, Tablefield, QueryFiled, "", "")

        'End Function
        'Public Function JoinLeft(Table As String, Tablefield As String) As SqlQuery(Of TResult)

        '    Return Me.JoinLeft(Table, Tablefield, Tablefield, "", "")

        'End Function
        'Public Function JoinLeft(q As Object, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

        '    'per aggiungere la where bisogna aggiungere anche i parametri
        '    Return Me.JoinLeft(q.GetCommand(SqlSelectCommndType.FROM), Tablefield, QueryFiled, "", "")

        'End Function
        'Public Function JoinLeft(q As Object, Tablefield As String) As SqlQuery(Of TResult)

        '    'per aggiungere la where bisogna aggiungere anche i parametri
        '    Return Me.JoinLeft(q, Tablefield, Tablefield)

        'End Function
        'Public Function JoinLeft(Table As String, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

        '    Return Me.Join(Table, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.LEFT)

        'End Function

        'Public Function JoinRight(Table As String, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

        '    Return Me.JoinRight(Table, Tablefield, QueryFiled, "", "")

        'End Function
        'Public Function JoinRight(Table As String, Tablefield As String) As SqlQuery(Of TResult)

        '    Return Me.JoinRight(Table, Tablefield, Tablefield, "", "")

        'End Function
        'Public Function JoinRight(q As Object, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

        '    'per aggiungere la where bisogna aggiungere anche i parametri
        '    Return Me.JoinRight(q.GetCommand(SqlSelectCommndType.FROM), Tablefield, QueryFiled, "", "")

        'End Function
        'Public Function JoinRight(q As Object, Tablefield As String) As SqlQuery(Of TResult)

        '    'per aggiungere la where bisogna aggiungere anche i parametri
        '    Return Me.Join(q, Tablefield, Tablefield)

        'End Function
        'Public Function JoinRight(Table As String, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

        '    Return Me.Join(Table, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.RIGHT)

        'End Function

        ' INNER JOIN
        Public Function Join(Table As String, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, QueryFiled, "", "", SqlJoinType.INNER)

        End Function
        Public Function Join(Table As String, Tablefield As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, Tablefield, "", "", SqlJoinType.INNER)

        End Function
        Public Function Join(Table As String, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Return Me.Join(Table, Tablefield, QueryFiled, TableAlias, Index, SqlJoinType.INNER)

        End Function
        Public Function Join(q As Object, Tablefield As String, QueryFiled As String) As SqlQuery(Of TResult)

            'Return Me.Join(q.GetCommand(SqlSelectCommndType.FROM), Tablefield, QueryFiled, "", "")

            'per aggiungere la where bisogna aggiungere anche i parametri
            Return Me.Join(q, Tablefield, QueryFiled, "", "", SqlJoinType.INNER)

        End Function
        Public Function Join(q As Object, Tablefield As String) As SqlQuery(Of TResult)

            'per aggiungere la where bisogna aggiungere anche i parametri
            Return Me.Join(q, Tablefield, Tablefield, "", "", SqlJoinType.INNER)

        End Function
        Public Function Join(q As Object, Tablefield As String, QueryField As String, TableAlias As String, Index As String) As SqlQuery(Of TResult)

            Return Me.Join(q, Tablefield, QueryField, TableAlias, Index, SqlJoinType.INNER)

        End Function



        ' join generiche
        Public Function Join(q As Object, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String, jt As SqlJoinType) As SqlQuery(Of TResult)

            Dim sqltext = Me.AddSqlQuery(q)

            Return Me.Join(" (" & sqltext & ") ", Tablefield, QueryFiled, TableAlias, Index, jt)

        End Function
        Public Function Join(Table As String, Tablefield As String, QueryFiled As String, TableAlias As String, Index As String, jt As SqlJoinType) As SqlQuery(Of TResult)

            Parse()

            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.FROM) Then
                Me._SqlParser.Add(SqlSelectCommndType.FROM, "")
            Else
                Select Case jt
                    Case SqlJoinType.INNER
                        Me._SqlParser(SqlSelectCommndType.FROM) &= " INNER JOIN "
                    Case SqlJoinType.LEFT
                        Me._SqlParser(SqlSelectCommndType.FROM) &= " LEFT JOIN "
                    Case SqlJoinType.RIGHT
                        Me._SqlParser(SqlSelectCommndType.FROM) &= " RIGHT JOIN "
                        'Case SqlJoinType.OUTER
                        '    Me._SqlParser(SqlSelectCommndType.FROM) &= " OUTER JOIN "
                End Select
            End If

            Me._SqlParser(SqlSelectCommndType.FROM) &= " " & Table & " "
            If TableAlias <> "" Then
                Me._SqlParser(SqlSelectCommndType.FROM) &= " as " & TableAlias & " "
                Table = TableAlias
            End If
            If Index <> "" Then
                Me._SqlParser(SqlSelectCommndType.FROM) &= " " & Index & " "
            End If

            If Tablefield = QueryFiled Then
                Me._SqlParser(SqlSelectCommndType.FROM) &= " USING (" & Tablefield & ")  "
            Else
                Me._SqlParser(SqlSelectCommndType.FROM) &= " ON " & Table & "." & Tablefield & " = " & QueryFiled & " "

            End If

            Me._CmdText = CreateSql()
            Return Me

        End Function

        'Public Function Where(Op As String, OpPar As String, ParamArray params() As Object) As SqlQuery(Of TResult)

        '    If params.Length > 0 Then

        '        Parse()

        '        Dim SqlWhereComm As Integer
        '        Select Case Me.SqlType
        '            Case SqlCommndType.SELECT
        '                SqlWhereComm = SqlSelectCommndType.WHERE
        '            Case SqlCommndType.UPDATE
        '                SqlWhereComm = SqlUpdateCommndType.WHERE
        '            Case SqlCommndType.DELETE
        '                SqlWhereComm = SqlDeleteCommndType.WHERE
        '        End Select

        '        If Not Me._SqlParser.ContainsKey(SqlWhereComm) Then
        '            Me._SqlParser.Add(SqlWhereComm, "")
        '        Else
        '            Me._SqlParser(SqlWhereComm) &= " " & Op & " "
        '        End If

        '        Me._SqlParser(SqlWhereComm) &= Me.Params(OpPar, params)

        '    End If

        '    Me._CmdText = CreateSql()
        '    Return Me


        'End Function

        Public Function WhereOp(Op As String, OpPar As String, ParamArray params() As Object) As SqlQuery(Of TResult)

            If params.Length > 0 Then

                Parse()

                Dim SqlWhereComm As Integer
                Select Case Me.SqlType
                    Case SqlCommndType.SELECT
                        SqlWhereComm = SqlSelectCommndType.WHERE
                    Case SqlCommndType.UPDATE
                        SqlWhereComm = SqlUpdateCommndType.WHERE
                    Case SqlCommndType.DELETE
                        SqlWhereComm = SqlDeleteCommndType.WHERE

                End Select


                If Not Me._SqlParser.ContainsKey(SqlWhereComm) Then
                    Me._SqlParser.Add(SqlWhereComm, "")
                Else
                    Me._SqlParser(SqlWhereComm) &= " " & Op & " "
                End If

                Me._SqlParser(SqlWhereComm) &= Me.Params(OpPar, params)

            End If

            Me._CmdText = CreateSql()
            Return Me


        End Function

        Public Function BuildCond(OpPar As String, ParamArray params() As Object) As SqlQuery(Of Object)
            Dim q As New SqlQuery(Of Object)(Me.DB)

            q.WhereOp("and", OpPar, params)

            Return q

        End Function

        Public Function Having(ParamArray params() As Object) As SqlQuery(Of TResult)

            Return HavingOp("and", "and", params)

        End Function
        'Public Function HavingOr(ParamArray params() As Object) As SqlQuery(Of TResult)

        '    Return HavingOp("or", "and", params)

        'End Function

        Public Function HavingOp(Op As String, OpPar As String, ParamArray params() As Object) As SqlQuery(Of TResult)

            If params.Length > 0 Then

                Parse()


                If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.HAVING) Then
                    Me._SqlParser.Add(SqlSelectCommndType.HAVING, "")
                Else
                    Me._SqlParser(SqlSelectCommndType.HAVING) &= " " & Op & " "
                End If

                Me._SqlParser(SqlSelectCommndType.HAVING) &= Me.Params(OpPar, params)

            End If

            Me._CmdText = CreateSql()
            Return Me


        End Function
        Public Function Where(ParamArray params() As Object) As SqlQuery(Of TResult)

            Return WhereOp("and", "and", params)

        End Function
        'Public Function WhereOr(ParamArray params() As Object) As SqlQuery(Of TResult)

        '    Return WhereOp("or", "and", params)

        'End Function

        Public Function OrderBy(ParamArray Fields() As String) As SqlQuery(Of TResult)

            Parse()

            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.ORDER_BY) Then
                Me._SqlParser.Add(SqlSelectCommndType.ORDER_BY, "")
            Else
                Me._SqlParser(SqlSelectCommndType.ORDER_BY) &= " , "
            End If

            For ct = 0 To Fields.Count - 1
                Me._SqlParser(SqlSelectCommndType.ORDER_BY) &= " " & Fields(ct) & " "
                If ct <> Fields.Count - 1 Then
                    Me._SqlParser(SqlSelectCommndType.ORDER_BY) &= " , "
                End If
            Next

            'Me._SqlParser(SqlSelectCommndType.ORDER_BY) &= " " & s & " "

            Me._CmdText = CreateSql()
            Return Me

        End Function

        Public Function GroupBy(ParamArray Fields() As String) As SqlQuery(Of TResult)

            Parse()

            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.GROUP_BY) Then
                Me._SqlParser.Add(SqlSelectCommndType.GROUP_BY, "")
            Else
                Me._SqlParser(SqlSelectCommndType.GROUP_BY) &= " , "
            End If

            For ct = 0 To Fields.Count - 1
                Me._SqlParser(SqlSelectCommndType.GROUP_BY) &= " " & Fields(ct) & " "
                If ct <> Fields.Count - 1 Then
                    Me._SqlParser(SqlSelectCommndType.GROUP_BY) &= " , "
                End If
            Next

            'Me._SqlParser(SqlSelectCommndType.ORDER_BY) &= " " & s & " "

            Me._CmdText = CreateSql()
            Return Me

        End Function


        Public Function NotifyChanged(nc As Boolean) As SqlQuery(Of TResult)

            Me._NotifyPropertyChanged = nc

            Return Me

        End Function

        Public Function Limit(ByVal n As Integer) As SqlQuery(Of TResult)

            Return Limit(n, Integer.MaxValue)

        End Function
        Public Function Limit(ByVal n As Integer, ByVal o As Integer) As SqlQuery(Of TResult)

            Parse()

            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.LIMIT) Then
                Me._SqlParser.Add(SqlSelectCommndType.LIMIT, "")
            End If

            Me._SqlParser(SqlSelectCommndType.LIMIT) = n.ToString
            If o <> Integer.MaxValue Then
                Me._SqlParser(SqlSelectCommndType.LIMIT) &= " OFFSET " & o.ToString
            End If

            Me._CmdText = CreateSql()
            Return Me


        End Function

        Public Function SetSelect(ParamArray Fields() As String) As SqlQuery(Of TResult)

            Me._SqlParser.Remove(SqlSelectCommndType.SELECT)
            Me._CmdText = CreateSql()
            Return Me.Select(Fields)

        End Function
        Public Function SetWhere(s As String) As SqlQuery(Of TResult)

            Me._SqlParser.Remove(SqlSelectCommndType.WHERE)
            Me._CmdText = CreateSql()
            Return Me.Where(s)

        End Function
        Public Function SetOrderBy(s As String) As SqlQuery(Of TResult)

            Me._SqlParser.Remove(SqlSelectCommndType.ORDER_BY)
            Me._CmdText = CreateSql()
            Return Me.OrderBy(s)

        End Function

        Public Function Update() As SqlQuery(Of TResult)

            Return Me.Update(Me._SqlParser(SqlSelectCommndType.FROM))

        End Function

        Public Function Update(Tab As String) As SqlQuery(Of TResult)

            Me.SqlType = SqlCommndType.UPDATE
            Parse()

            Me._SqlParser(SqlUpdateCommndType.UPDATE) = Tab

            Me._CmdText = CreateSql()

            Return Me

        End Function
        Public Function UpdateValue(ParamArray params() As Object) As SqlQuery(Of TResult)

            Parse()

            If Not Me._SqlParser.ContainsKey(SqlUpdateCommndType.UPDATE_SET) Then
                Me._SqlParser.Add(SqlUpdateCommndType.UPDATE_SET, "")
            Else
                Me._SqlParser(SqlUpdateCommndType.UPDATE_SET) &= " , "
            End If

            Me._SqlParser(SqlUpdateCommndType.UPDATE_SET) &= Me.ParamsUpdate(params)

            Me._CmdText = CreateSql()
            Return Me

        End Function

        Public Function Insert(Tab As String) As SqlQuery(Of TResult)

            Me.SqlType = SqlCommndType.INSERT
            Parse()
            Me._SqlMultiInsert.Clear()

            Me._SqlParser(SqlInsetCommndType.INSERT_INTO) = Tab

            Me._CmdText = CreateSql()

            Return Me

        End Function
        Public Function InsertValues(ParamArray params() As Object) As SqlQuery(Of TResult)

            Parse()

            If Not Me._SqlParser.ContainsKey(SqlInsetCommndType.INSERT_VALUES) Then
                Me._SqlParser.Add(SqlInsetCommndType.INSERT_VALUES, "")
                Me._SqlParser.Add(SqlInsetCommndType.INSERT_FIELD, "")
            Else
                Me._SqlParser(SqlInsetCommndType.INSERT_VALUES) &= " , "
                Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) &= " , "
            End If

            Dim isnull As Boolean
            For ct = 0 To params.Count - 1
                If ct Mod 2 = 0 Then

                    If ct <> 0 Then
                        Me._SqlParser(SqlInsetCommndType.INSERT_VALUES) &= " , "
                        Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) &= " , "
                    End If

                    isnull = False
                    If params(ct + 1) Is Nothing Then
                        isnull = True
                    Else
                        'If params(ct + 1).GetType = GetType(Date) Then
                        '    If params(ct + 1) = Date.MinValue Then
                        '        params(ct + 1) = "0000-00-00 00:00:00"
                        '        'isnull = True
                        '    End If
                        'End If

                    End If

                    If isnull Then
                        Me._SqlParser(SqlInsetCommndType.INSERT_VALUES) &= "NULL"
                    Else
                        Me._SqlParser(SqlInsetCommndType.INSERT_VALUES) &= Me.P(params(ct + 1))
                    End If

                    Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) &= params(ct)

                End If
            Next

            Me._CmdText = CreateSql()

            Return Me
        End Function

        'Public Sub InsertField(ParamArray params() As Object)

        '    Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) = ""
        '    For ct = 0 To params.Length - 1
        '        If ct <> 0 Then
        '            Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) &= ","
        '        End If
        '        Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) &= params(ct)
        '    Next

        '    Me._SqlMultiInsert.Clear()

        'End Sub

        'Public Sub InsertRowObject(o As TResult)
        '    ' TO DO
        '    ' prepare inserimento multiplo
        '    For Each field In DB.DBStructure(Me._SqlParser(SqlInsetCommndType.INSERT_INTO)).Fields
        '        q.InsertValues(field.Key, GetFieldValue(datains.Entity, field.Key))
        '    Next

        'End Sub

        Public Sub InsertMultiRow(ParamArray Values() As Object)

            'Me.sb.Append(s)
            'Return InsertSQL(False)


            _SqlMultiInsert.Append("(")

            For Each value As Object In Values
                Select Case value.GetType
                    Case GetType(String)
                        _SqlMultiInsert.Append(DBase.Conv.StrToDB(value))
                    Case GetType(DBNull)
                        _SqlMultiInsert.Append("NULL")
                    Case GetType(Double), GetType(Single)
                        _SqlMultiInsert.Append(DBase.Conv.DoubleToDB(value))
                    Case GetType(Date)
                        If value = Date.MinValue Then
                            _SqlMultiInsert.Append(DBase.Conv.StrToDB(Format(value, "0000-00-00 00:00:00")))
                        Else
                            _SqlMultiInsert.Append(DBase.Conv.StrToDB(Format(value, "yyyy-MM-dd HH:mm:ss")))
                        End If
                    Case Else
                        _SqlMultiInsert.Append(value)

                End Select
                _SqlMultiInsert.Append(",")
            Next
            _SqlMultiInsert.Remove(_SqlMultiInsert.Length - 1, 1)

            _SqlMultiInsert.Append("),")


            'Return InsertSQL(SqlIns.ToString)

        End Sub
        Public Sub ExecuteMultiInsert()
            Dim Sql As New Text.StringBuilder

            If _SqlMultiInsert.Length = 0 Then Exit Sub


            Dim b As Integer = 0
            Dim e As Integer = 0

            _SqlMultiInsert.Remove(_SqlMultiInsert.Length - 1, 1)

            Do
                e = b + 100000
                If _SqlMultiInsert.Length < e Then
                    e = _SqlMultiInsert.Length - 1
                End If

                Do Until _SqlMultiInsert.Chars(e) = ")"
                    e += 1
                Loop

                Sql.Clear()
                Sql.Append("Insert into " & Me._SqlParser(SqlInsetCommndType.INSERT_INTO))
                If Me._SqlParser.ContainsKey(SqlInsetCommndType.INSERT_FIELD) Then
                    Sql.Append(" (" & Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) & ") ")
                End If
                Sql.Append(" values ")
                Sql.Append(_SqlMultiInsert.ToString(b, e - b + 1))

                Me._CmdText = Sql.ToString
                Me.ExecuteCommand()

                If e < _SqlMultiInsert.Length - 1 Then
                    Do Until _SqlMultiInsert.Chars(e) = "("
                        e += 1
                    Loop
                    b = e
                End If

            Loop Until e = _SqlMultiInsert.Length - 1

            _SqlMultiInsert.Clear()

        End Sub

        Public Function Delete(Tab As String) As SqlQuery(Of TResult)

            Me.SqlType = SqlCommndType.DELETE
            Parse()

            Me._SqlParser(SqlDeleteCommndType.DELETE) = Tab
            If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.FROM) Then
                Me._SqlParser.Add(SqlSelectCommndType.FROM, Tab)
            End If

            Me._CmdText = CreateSql()

            Return Me

        End Function
        Public Function Delete() As SqlQuery(Of TResult)

            Return Me.Delete(Me._SqlParser(SqlSelectCommndType.FROM))

        End Function
        Private Sub Parse()

            If Me._SqlParser.Count > 0 Then
                Exit Sub
            End If

            Dim tmpsql As String = " " & Me.CmdText.ToUpper
            For ct As Integer = SqlCommList(Me.SqlType).Length - 1 To 0 Step -1
                AddComm(tmpsql, ct)
            Next

            'se non c'è il select lo aggiunge
            If Me.SqlType = SqlCommndType.SELECT Then
                If Not Me._SqlParser.ContainsKey(SqlSelectCommndType.SELECT) Then
                    Me._SqlParser.Add(SqlSelectCommndType.SELECT, "*")
                End If
            End If

        End Sub
        Private Sub AddComm(ByRef sql As String, ct As Integer)
            Dim c = SqlCommList(Me.SqlType)(ct)
            Dim sqlcomm As String
            Dim BeginPos As Integer

            If c = "" Then Exit Sub

            Select Case Me.SqlType
                Case SqlCommndType.SELECT
                    Select Case ct
                        Case SqlSelectCommndType.SELECT, SqlSelectCommndType.FROM
                            BeginPos = sql.IndexOf(" " & c & " ")
                        Case Else
                            BeginPos = sql.LastIndexOf(" " & c & " ")
                            If BeginPos < sql.IndexOf(" as ") Then
                                BeginPos = -1
                            End If
                    End Select
                Case Else
                    BeginPos = sql.IndexOf(" " & c & " ")

            End Select


            If BeginPos <> -1 Then
                sqlcomm = sql.Substring(Len(c) + 1 + BeginPos).Trim

                Select Case Me.SqlType
                    Case SqlCommndType.INSERT
                        Select Case ct
                            Case SqlInsetCommndType.INSERT_INTO
                                Dim i = sqlcomm.IndexOf("(")
                                If i <> -1 Then
                                    _SqlParser(SqlInsetCommndType.INSERT_INTO) = sqlcomm.Substring(0, i).Trim
                                    _SqlParser(SqlInsetCommndType.INSERT_FIELD) = sqlcomm.Substring(i + 1).Trim({" "c, "("c, ")"c})
                                End If
                            Case SqlInsetCommndType.INSERT_VALUES
                                _SqlParser.Add(ct, sqlcomm.Trim({"("c, ")"c}))
                        End Select

                    Case Else
                        _SqlParser.Add(ct, sqlcomm)

                End Select

                sql = sql.Substring(0, BeginPos + 1)
            End If

        End Sub
        Private Function CreateSql() As String
            Dim tmpsql As String = ""

            'If Me.SqlType = SqlCommndType.SELECT And Not _SqlParser.ContainsKey(SqlSelectCommndType.SELECT) Then
            'End If

            For ct As Integer = 0 To SqlCommList(Me.SqlType).Length - 1
                If _SqlParser.ContainsKey(ct) Then

                    Select Case Me.SqlType
                        Case SqlCommndType.INSERT
                            Select Case ct
                                Case SqlInsetCommndType.INSERT_INTO
                                    tmpsql &= SqlCommList(Me.SqlType)(ct) & " " & Me._SqlParser(ct) & " "
                                    If Me._SqlParser.ContainsKey(SqlInsetCommndType.INSERT_FIELD) Then
                                        tmpsql &= " (" & Me._SqlParser(SqlInsetCommndType.INSERT_FIELD) & ") "
                                    End If

                                Case SqlInsetCommndType.INSERT_VALUES
                                    tmpsql &= SqlCommList(Me.SqlType)(ct) & " (" & Me._SqlParser(ct) & ") "
                                Case SqlInsetCommndType.INSERT_FIELD
                            End Select

                        Case Else
                            tmpsql &= SqlCommList(Me.SqlType)(ct) & " " & Me._SqlParser(ct) & " "

                    End Select

                End If
            Next

            Return tmpsql

        End Function

        Public Function GetCommand(t As SqlSelectCommndType) As String

            Me.Parse()

            Return Me._SqlParser(t)

        End Function

        Public Function ExecuteCommand() As Integer

            Return Me.ExecuteCommand(False)

        End Function

        Public Function ExecuteCommand(GetLastID As Boolean) As Integer
            Dim CnnClose As Boolean = False

            Try

                'If Me.Cnn.State = ConnectionState.Closed Then
                '    Me.Cnn.Open()
                '    CnnClose = True
                'End If

                'Me.Command.CommandText = Me.CmdText

                'Dim res = Me.Command.ExecuteNonQuery
                Dim res As Integer

                If GetLastID Then
                    res = MySqlHelper.ExecuteScalar(Me.CnnString, Me.CmdText & ";SELECT LAST_INSERT_ID();", Me.Parameters.ToArray)

                    DB._LastID = res 'Me.Sql("SELECT LAST_INSERT_ID()").ToObj(Of UInteger)()
                Else
                    res = MySqlHelper.ExecuteNonQuery(Me.CnnString, Me.CmdText, Me.Parameters.ToArray)

                End If

                Debug.Print(Me.CmdText)

                Return res

            Catch ex As Exception
                Dim TempCmd = Me.CmdText

                DB.ResetChanges()

                Throw New Exception("Errore MySQL:" & TempCmd, ex)

            Finally
                'If CnnClose Then
                '    Me.Cnn.Close()
                'End If
            End Try

        End Function
        Public Function ToDataTable() As DataTable
            'Dim Dtb As New DataTable
            'Dim DtSet As New DataSet
            Dim CnnClose As Boolean = False

            Try

                If Me.DT Is Nothing Or Me._LastCmdText <> Me.CmdText Then



                    'If Me.Cnn.State = ConnectionState.Closed Then
                    '    Me.Cnn.Open()
                    '    CnnClose = True
                    'End If
                    'Me.Command.CommandText = Me.CmdText

                    Me.DT = MySqlHelper.ExecuteDataset(Me.CnnString, Me.CmdText, Me.Parameters.ToArray).Tables(0)

                    'Dim MySqlDTA As New MySqlDataAdapter(Me.Command)
                    'Me.DT = New DataTable
                    'MySqlDTA.Fill(Me.DT)


                    Me._LastCmdText = Me.CmdText

                    Debug.Print(Me.CmdText)

                End If


                Return Me.DT

            Catch ex As Exception

                DB.ResetChanges()

                Throw New Exception("Errore MySQL:" & Me.CmdText, ex)
            Finally
                'If CnnClose Then
                '    Me.Cnn.Close()
                'End If
            End Try

        End Function
        'Public Function GetLastID() As UInteger
        '    'Dim q = Me.GetDBQuery

        '    Return Me.Sql("SELECT LAST_INSERT_ID()").ToObj(Of UInteger)()

        'End Function



        'Public Function ToList() As List(Of TResult)

        '    Return Me.ToList(Of TResult)()

        'End Function

        'Public Function ToList(ExecQuery As Boolean) As List(Of TResult)

        '    Return Me.ToList(Of TResult)(ExecQuery)

        'End Function

        'Public Function ToList(Of TResultf As {New, Class})() As List(Of TResultf)

        '    Return Me.ToList(Of TResultf)(True)

        'End Function
        'Public Function ToList(Of TResultf As {New, Class})() As List(Of TResultf)

        '    Dim t = GetType(TResultf)
        '    Dim obj As TResultf
        '    Dim r As New List(Of TResultf)


        '    'If ExecQuery Then
        '    Me.ToDataTable()
        '    'End If

        '    Dim NotifyPropertyChanged = t.GetInterface("INotifyPropertyChanged") IsNot Nothing

        '    If t <> GetType(Object) Then
        '        Dim PropList = DBase.Data.GetListPropObjectDataTable(DT, t)

        '        For Each dr As DataRow In DT.Rows
        '            obj = New TResultf
        '            DBase.Data.DataRowToObject(dr, obj, PropList)
        '            If NotifyPropertyChanged Then
        '                Me.DB.AddChangedHandle(obj)
        '            End If
        '            r.Add(obj)
        '        Next

        '    Else

        '        For Each dr As DataRow In DT.Rows
        '            r.Add(dr(0))
        '            'Dim ooo As Object = New With {.aa = dr(0)}
        '            'r.Add(ooo)
        '        Next

        '    End If


        '    Return r

        'End Function
        'Public Function ToListP(Of TResultf)(Field As String) As List(Of TResultf)

        '    Me.ToDataTable()

        '    Return Utils.DT.ToList(Of TResultf)(Me.DT, Field)

        'End Function

        Public Function ToList() As List(Of TResult)

            Return Me.ToList(Of TResult)()

        End Function
        Public Function ToList(Of TResultf)() As List(Of TResultf)

            If GetType(TResultf) <> GetType(TResult) Then
                Me.TabUpdateName = DBContext.GetTableName(GetType(TResultf))
            End If

            Return Me.ToList(Of TResultf)(Nothing)

        End Function

        Private Function IsNotifyPropertyChanged(t As Type) As Boolean

            Return t.GetInterface("INotifyPropertyChanged") IsNot Nothing And Me._NotifyPropertyChanged

        End Function

        Public Function ToList(Of TResultf)(FieldObj As String) As List(Of TResultf)

            Dim t = GetType(TResultf)
            Dim obj As TResultf
            Dim r As New List(Of TResultf)

            'If ExecQuery Then
            Me.ToDataTable()
            'End If

            If Not t.IsValueType And t <> GetType(String) Then
                Dim PropList = DBase.Data.GetListPropObjectDataTable(DT, t).Where(Function(o) o.CanWrite).ToList
                Dim info As Reflection.ConstructorInfo = t.GetConstructor(New Type() {})
                Dim NotifyPropertyChanged = IsNotifyPropertyChanged(t)

                For Each dr As DataRow In DT.Rows
                    obj = info.Invoke(Nothing)

                    DBase.Data.DataRowToObject(dr, obj, PropList)

                    'IMPORTANTE PRIMA DI METTTRE LE PROP AGGANCIA IL DB
                    If NotifyPropertyChanged Then
                        'Me.DB.AddChangedHandle(obj)
                        Me.AddChangedHandle(obj)
                    End If

                    r.Add(obj)
                Next

            Else

                r = Utils.DT.ToList(Of TResultf)(Me.DT, FieldObj)

            End If


            Return r

        End Function

        Public Function ToDictionary(Of TKey)(KeyField As String) As Dictionary(Of TKey, TResult)

            Return Me.ToDictionary(Of TKey, TResult)(KeyField)

        End Function

        Public Function ToDictionary(Of TKey, TResultf As {New, Class})(KeyField As String) As Dictionary(Of TKey, TResultf)

            Dim t = GetType(TResultf)
            Dim obj As TResultf
            Dim r As New Dictionary(Of TKey, TResultf)
            Dim k As TKey

            Me.ToDataTable()

            Dim PropList = DBase.Data.GetListPropObjectDataTable(DT, t)
            Dim NotifyPropertyChanged = IsNotifyPropertyChanged(t)

            For Each dr As DataRow In DT.Rows
                k = dr(KeyField)
                If Not r.ContainsKey(k) Then
                    obj = New TResultf
                    DBase.Data.DataRowToObject(dr, obj, PropList)
                    If NotifyPropertyChanged Then
                        'Me.DB.AddChangedHandle(obj)
                        Me.AddChangedHandle(obj)
                    End If
                    r.Add(k, obj)
                End If
                'r.Add(obj)
            Next


            Return r

        End Function

        Public Function ToObj() As TResult

            Return Me.ToObj(Of TResult)()

        End Function
        Public Function ToObj(Of TResultf)() As TResultf


            Dim o = ToList(Of TResultf)()

            If o.Count = 0 Then
                Return Nothing
            Else
                Return o(0)
            End If

        End Function


        Public Sub AddChangedHandle(e As System.ComponentModel.INotifyPropertyChanged)

            AddHandler e.PropertyChanged, AddressOf Me.PropertyChanged

        End Sub

        Private Sub PropertyChanged(S As Object, e As System.ComponentModel.PropertyChangedEventArgs)

            DB.UpdateOnSubmit(TabUpdateName, S, e.PropertyName)

        End Sub
        Public Function Reset() 'As SqlQuery(Of TResult)

            'Me._MergeData = add

            Me.Parameters.Clear()
            Me._CmdText = ""
            Me._LastCmdText = ""
            Me._SqlParser.Clear()
            Me.SqlType = SqlCommndType.SELECT
            'If Not _MergeData Then
            Me.DT = Nothing
            'End If
            Me.TabUpdateName = ""


            Return Me

        End Function
        'Public Function Reset() 'As SqlQuery(Of TResult)

        '    'Me._MergeData = add

        '    Me.Command.Parameters.Clear()
        '    Me._CmdText = ""
        '    Me._LastCmdText = ""
        '    Me._SqlParser.Clear()
        '    Me.SqlType = SqlCommndType.SELECT
        '    'If Not _MergeData Then
        '    Me.DT = Nothing
        '    'End If
        '    Me.TabUpdateName = ""


        '    Return Me

        'End Function
        'Public Function Reset() As SqlQuery(Of TResult)

        '    Return Me.Reset(False)


        'End Function


        'Public Function Clone() As SqlQuery(Of TResult)
        '    Dim q As SqlQuery(Of TResult) = Me.MemberwiseClone

        '    'q._CmdText = Me.CmdText
        '    q._SqlParser = New Dictionary(Of Integer, String)
        '    q.Command = Me.Command.Clone

        '    Return q

        'End Function
        Public Function Clone() As SqlQuery(Of TResult)
            Dim q As SqlQuery(Of TResult) = Me.MemberwiseClone

            'q._CmdText = Me.CmdText

            q._SqlParser = New Dictionary(Of Integer, String)
            For Each s In Me._SqlParser
                q._SqlParser.Add(s.Key, s.Value)
            Next
            'q.Command = Me.Command.Clone
            q.Parameters = New List(Of MySqlParameter)
            For Each Par In Me.Parameters
                q.Parameters.Add(Par.Clone)
            Next

            Return q

        End Function

        'Public Function GetTable(Of TResultf As {New, Class})() As TResultf

        '    Dim tt As Object = DB.GetTable(Of TResultf)()

        '    tt.Sql(Me.CmdText)
        '    tt.Command = Me.Command


        '    Return tt

        'End Function


    End Class

    'Public MustInherit Class EntityBase
    '    Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged
    '    Private Shared emptyChangingEventArgs As System.ComponentModel.PropertyChangingEventArgs = New System.ComponentModel.PropertyChangingEventArgs("")



    '    Public Function Clone() As EntityBase
    '        Dim entity As EntityBase = Me.MemberwiseClone()
    '        RemoveHandler entity.PropertyChanged, Me.PropertyChangedEvent
    '        Return entity
    '    End Function

    '    Public Event PropertyChanging As System.ComponentModel.PropertyChangingEventHandler Implements System.ComponentModel.INotifyPropertyChanging.PropertyChanging
    '    Public Event PropertyChanged As System.ComponentModel.PropertyChangedEventHandler Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged
    '    Protected Overridable Sub SendPropertyChanging()
    '        Dim h As System.ComponentModel.PropertyChangingEventHandler = Me.PropertyChangingEvent
    '        If (Not (h) Is Nothing) Then
    '            h(Me, emptyChangingEventArgs)
    '        End If
    '    End Sub
    '    Protected Overridable Sub SendPropertyChanged(ByVal propertyName As String)
    '        Dim h As System.ComponentModel.PropertyChangedEventHandler = Me.PropertyChangedEvent
    '        If (Not (h) Is Nothing) Then
    '            h(Me, New System.ComponentModel.PropertyChangedEventArgs(propertyName))
    '        End If
    '    End Sub
    'End Class


    Public MustInherit Class DBStructureBase
        Inherits Dictionary(Of String, DBContext.TableProperty)

        Public Sub New()
            MyBase.New(StringComparer.CurrentCultureIgnoreCase)
        End Sub

    End Class


    'Public Class MySqlLinQ

    '    Public CmdText As String
    '    Public dc As DataContext
    '    Public Params As New List(Of Object)

    '    Public Sub New(ByVal DB As DataContext)

    '        Me.dc = DB

    '    End Sub

    '    Public Function AddPar(ByVal Param As Object) As String

    '        Dim TextParam As String = "{" & (Me.Params.Count).ToString & "}"
    '        Me.Params.Add(Param)

    '        Return TextParam
    '    End Function
    '    Public Function ExecuteQuery(Of TResult As {New, Class})(ByVal query As String) As List(Of TResult)

    '        Return (dc.ExecuteQuery(Of TResult)(query, Params.ToArray)).ToList

    '    End Function


    'End Class

    Public Class FastSqlInsert

        Public sb As New Text.StringBuilder()
        Public DB As DBContext
        Public Tabella As String
        Public BeginSqlText As String = ""
        Public EndSqlText As String = ""

        Public Sub New(ByRef oDB As DBContext, ByVal Tab As String)

            Me.DB = oDB
            Me.Tabella = Tab

        End Sub
        Public Sub New(ByRef oDB As DBContext)

            Me.DB = oDB

        End Sub

        'Public Sub AddSQL(ByVal s As String)

        '    Me.sb.Append(s)
        '    InsertSQL()

        'End Sub

        'Public Function InsertSQL() As Boolean

        '    Return InsertSQL(False)

        'End Function

        Public Function InsertSQL(ByVal s As String) As Boolean

            Me.sb.Append(s)
            Return InsertSQL(False)

        End Function
        Public Function InsertValues(ParamArray Values() As Object) As Boolean

            'Me.sb.Append(s)
            'Return InsertSQL(False)
            Dim SqlIns As New Text.StringBuilder

            SqlIns.Append("(")

            For Each value As Object In Values
                Select Case value.GetType
                    Case GetType(String)
                        SqlIns.Append(DBase.Conv.StrToDB(value))
                    Case GetType(DBNull)
                        SqlIns.Append("NULL")
                    Case Else
                        SqlIns.Append(DBase.Conv.DoubleToDB(value))

                End Select
                SqlIns.Append(",")
            Next
            SqlIns.Remove(SqlIns.Length - 1, 1)

            SqlIns.Append("),")

            Return InsertSQL(SqlIns.ToString)

        End Function


        Public Function InsertSQl(ByVal Forza As Boolean) As Boolean

            If (sb.Length > 50000) Or (Forza And sb.Length > 0) Then
                sb.Remove(sb.Length - 1, 1)

                If Me.EndSqlText <> "" Then
                    sb.Append(" " & Me.EndSqlText & " ")
                End If

                sb.Append(";")

                If Me.BeginSqlText = "" Then
                    sb.Insert(0, SqlInsertText(Tabella))
                Else
                    sb.Insert(0, " " & Me.BeginSqlText & " ")
                End If

                DB.ExecuteCommand(sb.ToString)
                sb = New Text.StringBuilder()
                Return True
            Else
                Return False
            End If

        End Function
        Public Function FinalSQL() As Boolean

            Return InsertSQL(True)

        End Function

        Public Function SqlInsertText(ByVal tabella As String) As String

            Return "Insert into " & tabella & " values "

        End Function


    End Class

    Namespace Tools

        Public Class CreateDbClass


            Public filepath As String
            Public NameSpaceDB As String
            Public ClassNameDB As String
            Public DBName As String
            Public ConnStr As String

            Private cnn As MySqlConnection

            Dim SClass As IO.StreamWriter

            'Public Sub New(conn As String)

            '    Me.ConnStr = conn
            '    cnn = New MySqlConnection(Me.ConnStr)

            'End Sub
            Public Sub New()



            End Sub

            Public Sub CreateClass()

                'cnn = New MySqlConnection(Me.ConnStr)

                Dim q = New SqlQuery(Of Object)(Me.ConnStr)

                SClass = IO.File.CreateText(filepath)
                SClass.WriteLine("Imports DBase.MySql")
                'SClass.WriteLine("Imports System.Data.Linq.Mapping" & vbCrLf & "Imports System.Diagnostics" & vbCrLf & "Imports DBase.MySql")

                Dim DT_tables As New DataTable

                DT_tables = q.Sql("show TABLES").ToDataTable

                SClass.WriteLine(
                    "Namespace " & NameSpaceDB & vbCrLf &
                        "Partial Public Class " & ClassNameDB & vbCrLf &
                        "Inherits DBContext" & vbCrLf
                )


                SClass.WriteLine(
                        "Public Sub New(ByVal CnnString As String, DBStructure As DBStructureBase)" & vbCrLf &
                            "MyBase.New(CnnString, DBStructure)" & vbCrLf &
                            "Me.DBName = """ & Me.DBName & """" & vbCrLf &
                        "End Sub"
                )

                SClass.WriteLine(
                        GetProperty("TAB", "helper.tab", True, True, "(Me)") & vbCrLf &
                        GetProperty("VIEW", "helper.view", True, True, "(me)")
                )

                '                        GetProperty( "Public Property TAB As New helper.tab(Me)"

                SClass.WriteLine(
                        "Public Function GetBuildQuery(Of r As {New, Class})() As BuildQuery(Of r)" & vbCrLf &
                            "Return New BuildQuery(Of r)(Me)" & vbCrLf &
                        "End Function"
                )

                helper(DT_tables)


                SClass.WriteLine(
                        "Partial Public Class BuildQuery(Of Tresult As {New, Class})" & vbCrLf &
                            "Inherits TableQuery(Of Tresult)" & vbCrLf &
                            "Public Sub New(db As DBContext)" & vbCrLf &
                                "MyBase.New(db)" & vbCrLf &
                            "End Sub" & vbCrLf &
                            "Public Sub New(db As DBContext,tb as string)" & vbCrLf &
                                "MyBase.New(db, tb)" & vbCrLf &
                            "End Sub" & vbCrLf &
                            GetProperty("REL", "helper.rel(Of Tresult)", True, True, "(me)") & vbCrLf &
                            GetProperty("BY", "helper.by(Of Tresult)", True, True, "(me)") & vbCrLf
                )

                '"Public Sub New(db As DBContext,tb as string)" & vbCrLf &
                '    "MyBase.New(db,tb)" & vbCrLf &
                '"End Sub" & vbCrLf &


                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    'table_function(DT_tables.Rows(tables)(0))

                Next

                SClass.WriteLine(
                        "End Class"
                )


                'table_function



                SClass.WriteLine("#Region ""Prop Tabelle DB""")

                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    'table_prop(DT_tables.Rows(tables)(0))

                Next

                SClass.WriteLine("#End Region" & vbCrLf)


                'SClass.WriteLine("#Region ""Classi Tabelle DB""")

                'For tables As Integer = 0 To DT_tables.Rows.Count - 1

                '    table_class(DT_tables.Rows(tables)(0))

                'Next

                'SClass.WriteLine("#End Region" & vbCrLf)

                SClass.WriteLine("#Region ""Tabelle DB""")

                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    table_field(DT_tables.Rows(tables)(0))

                Next

                SClass.WriteLine("#End Region" & vbCrLf)

                SClass.WriteLine("#Region ""struttura DB""")

                SClass.WriteLine(
                    "Public Class [Structure]" & vbCrLf &
                    "Inherits DBStructureBase" & vbCrLf &
                    "Public Sub New()" & vbCrLf &
                    "Dim newtab As New DBContext.TableProperty" & vbCrLf
                )


                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    table_struct(DT_tables.Rows(tables)(0))

                Next

                SClass.WriteLine(
                    "End Sub" & vbCrLf &
                    "End Class" & vbCrLf
                )

                SClass.WriteLine("#End Region" & vbCrLf)


                SClass.WriteLine(
                    "End Class" & vbCrLf &
                    "End Namespace"
                )

                SClass.Close()


            End Sub

            Private Function GetProperty(Name As String, Type As String, onlyread As Boolean, cache As Boolean, param_new As String) As String



                Return (
                    "Private _" & Name & " As " & Type & "" & vbCrLf &
                    "Public ReadOnly Property " & Name & "() As " & Type & "" & vbCrLf &
                        "Get" & vbCrLf &
                            "If _" & Name & " Is Nothing Then" & vbCrLf &
                                "_" & Name & " = New " & Type & "" & param_new & "" & vbCrLf &
                            "End If" & vbCrLf &
                            "Return _" & Name & "" & vbCrLf &
                        "End Get" & vbCrLf &
                    "End Property" & vbCrLf
                )

            End Function


            Private Sub table_prop(s As String)

                s = StrConv(s, VbStrConv.ProperCase)
                SClass.WriteLine(
                    "Public ReadOnly Property Tab_" & s & "() As BuildQuery(Of " & s & ")" & vbCrLf &
                        "Get" & vbCrLf &
                            "Return GetBuildQuery(Of " & s & ")() " & vbCrLf &
                        "End Get" & vbCrLf &
                    "End Property"
                )

                '                    "Return GetTable(Of T" & s & "(Of " & s & "))()" & vbCrLf &

                ' Return GetTab(Of Account)()

            End Sub
            Private Sub table_class(s As String)

                's = StrConv(s, VbStrConv.ProperCase)
                'SClass.WriteLine(
                '    "Partial Public Class t" & s & "(Of Tresult As {New, Class}) " & vbCrLf &
                '    "Inherits TableQuery(Of Tresult) " & vbCrLf &
                '    "End Class"
                ')

                '"Public Property fN As New fn" & s & "" & vbCrLf &
                '"Public Property fTN As New ftn" & s & "" & vbCrLf &

            End Sub


            Private Sub helper(DT_tables As DataTable)
                Dim q = New SqlQuery(Of Object)(Me.ConnStr)

                SClass.WriteLine(
                    "Partial Class helper" & vbCrLf
                )

                '--------------------------------------------------
                'TABELLE

                SClass.WriteLine(
                        "Public Class tab" & vbCrLf &
                            "Private db As DBase.MySql.DBContext" & vbCrLf &
                            "Public Sub New(db As DBase.MySql.DBContext)" & vbCrLf &
                                "Me.db = db" & vbCrLf &
                            "End Sub" & vbCrLf
                )


                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    Dim s = DT_tables.Rows(tables)(0)
                    s = StrConv(s, VbStrConv.ProperCase)

                    SClass.WriteLine(
                            "Public ReadOnly Property " & s & "() As BuildQuery(Of " & s & ")" & vbCrLf &
                                "Get" & vbCrLf &
                                    "Return New BuildQuery(Of " & s & ")(db)" & vbCrLf &
                                "End Get" & vbCrLf &
                            "End Property" & vbCrLf
                    )


                    Dim DT_fields = q.Sql("DESCRIBE " & s).ToDataTable
                    For Each dr In DT_fields.Rows
                        dr("field") = StrConv(dr("field"), VbStrConv.ProperCase)
                    Next

                Next


                SClass.WriteLine(
                        "End Class" & vbCrLf
                )

                '--------------------------------------------------
                'VIEW
                SClass.WriteLine(
                        "Partial Class view" & vbCrLf &
                            "Private db As " & Me.ClassNameDB & "" & vbCrLf &
                            "Public Sub New(db As " & Me.ClassNameDB & ")" & vbCrLf &
                                "Me.db = db" & vbCrLf &
                            "End Sub" & vbCrLf
                )

                ''proprerty
                'For tables As Integer = 0 To DT_tables.Rows.Count - 1

                '    Dim s = DT_tables.Rows(tables)(0)
                '    s = StrConv(s, VbStrConv.ProperCase)

                '    SClass.WriteLine(
                '            GetProperty(s.Replace("_", ""), "helper." & s.Replace("_", "") & "", True, True, "(db)") & vbCrLf
                '    )
                '    '                            "Public Property " & s.Replace("_", "") & "() As New T" & s.Replace("_", "") & "(q)" & vbCrLf

                'Next

                ''classe helper
                'SClass.WriteLine(
                '            "Public Class helper" & vbCrLf
                ')

                'For tables As Integer = 0 To DT_tables.Rows.Count - 1

                '    Dim s = DT_tables.Rows(tables)(0)
                '    s = StrConv(s, VbStrConv.ProperCase)



                '    SClass.WriteLine(
                '                "Partial Public Class " & s.Replace("_", "") & "" & vbCrLf &
                '                    "Private db As " & Me.ClassNameDB & "" & vbCrLf &
                '                    "Public Sub New(db As " & Me.ClassNameDB & ")" & vbCrLf &
                '                        "Me.db = db" & vbCrLf &
                '                    "End Sub" & vbCrLf &
                '                "End Class" & vbCrLf
                '    )


                '    Dim DT_fields = q.Sql("DESCRIBE " & s).ToDataTable
                '    For Each dr In DT_fields.Rows
                '        dr("field") = StrConv(dr("field"), VbStrConv.ProperCase)
                '    Next

                'Next
                'SClass.WriteLine(
                '            "End Class" & vbCrLf
                ')

                'fine view
                SClass.WriteLine(
                        "End Class" & vbCrLf
                )
                '--------------------------------------------------
                'FINE VIEW



                '--------------------------------------------------
                'RELAZIONI

                SClass.WriteLine(
                        "Partial Class rel(Of Tresult As {New, Class})" & vbCrLf &
                            "Private q As BuildQuery(Of Tresult)" & vbCrLf &
                            "Public Sub New(q As BuildQuery(Of Tresult))" & vbCrLf &
                                "Me.q = q" & vbCrLf &
                            "End Sub" & vbCrLf
                )

                'proprerty
                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    Dim s = DT_tables.Rows(tables)(0)
                    s = StrConv(s, VbStrConv.ProperCase)

                    SClass.WriteLine(
                            GetProperty(s, "helper." & s & "", True, True, "(q)") & vbCrLf
                    )
                    '                            "Public Property " & s.Replace("_", "") & "() As New T" & s.Replace("_", "") & "(q)" & vbCrLf

                Next

                'classe helper
                SClass.WriteLine(
                            "Public Class helper" & vbCrLf
                )

                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    Dim s = DT_tables.Rows(tables)(0)
                    s = StrConv(s, VbStrConv.ProperCase)



                    SClass.WriteLine(
                                "Partial Public Class " & s & "" & vbCrLf &
                                    "Private q As BuildQuery(Of Tresult)" & vbCrLf &
                                    "Public Sub New(q As BuildQuery(Of Tresult))" & vbCrLf &
                                        "Me.q = q" & vbCrLf &
                                    "End Sub" & vbCrLf &
                                "End Class" & vbCrLf
                    )


                    Dim DT_fields = q.Sql("DESCRIBE " & s).ToDataTable
                    For Each dr In DT_fields.Rows
                        dr("field") = StrConv(dr("field"), VbStrConv.ProperCase)
                    Next

                Next
                SClass.WriteLine(
                            "End Class" & vbCrLf
                )

                'fine rel
                SClass.WriteLine(
                        "End Class" & vbCrLf
                )
                '--------------------------------------------------
                'FINE RELAZIONI


                '--------------------------------------------------
                'FILTRI

                SClass.WriteLine(
                        "Partial Class by(Of Tresult As {New, Class})" & vbCrLf &
                            "Private q As BuildQuery(Of Tresult)" & vbCrLf &
                            "Public Sub New(q As BuildQuery(Of Tresult))" & vbCrLf &
                                "Me.q = q" & vbCrLf &
                            "End Sub" & vbCrLf
                )

                'proprerty
                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    Dim s = DT_tables.Rows(tables)(0)
                    s = StrConv(s, VbStrConv.ProperCase)

                    SClass.WriteLine(
                            GetProperty(s, "helper." & s & "", True, True, "(q)") & vbCrLf
                    )

                Next

                'classe helper
                SClass.WriteLine(
                            "Public Class helper" & vbCrLf
                )

                For tables As Integer = 0 To DT_tables.Rows.Count - 1

                    Dim s = DT_tables.Rows(tables)(0)
                    s = StrConv(s, VbStrConv.ProperCase)

                    SClass.WriteLine(
                            "Partial Public Class " & s & "" & vbCrLf &
                                "Private q As BuildQuery(Of Tresult)" & vbCrLf &
                                "Public Sub New(q As BuildQuery(Of Tresult))" & vbCrLf &
                                    "Me.q = q" & vbCrLf &
                                "End Sub" & vbCrLf
                    )


                    Dim DT_fields = q.Sql("DESCRIBE " & s).ToDataTable
                    Dim FattoPri = False
                    For Each dr In DT_fields.Rows
                        dr("field") = StrConv(dr("field"), VbStrConv.ProperCase)

                        If dr("Key") = "MUL" Or dr("Key") = "UNI" Or (dr("Key") = "PRI" And Not FattoPri) Then

                            Dim Type = to_mysql(dr("Type").ToString)
                            Dim type_dotnet = to_dotnet(Type)

                            SClass.WriteLine(
                                "Public Function " & dr("field") & "(v As " & type_dotnet & ") As BuildQuery(Of Tresult)" & vbCrLf &
                                        "Return q.Where(""" & s & "." & dr("field") & """, ""="", v)" & vbCrLf &
                                "End Function" & vbCrLf
                            )

                            If dr("Key") = "PRI" Then
                                FattoPri = True
                            End If

                        End If
                    Next

                    SClass.WriteLine(
                            "End Class" & vbCrLf
                    )

                Next
                SClass.WriteLine(
                            "End Class" & vbCrLf
                )

                'fine filtri
                SClass.WriteLine(
                        "End Class" & vbCrLf
                )


                ' end helper
                SClass.WriteLine(
                    "End Class" & vbCrLf
                )

            End Sub

            Private Sub table_function(s As String)


                Dim q = New SqlQuery(Of Object)(Me.ConnStr)


                Dim DT_fields As New DataTable

                Dim type As String = 0
                Dim type_dotnet As String = 0
                Dim null As String = ""
                Dim auto_gen As String = ""
                Dim key As String = ""
                Dim dbtype As String = ""

                DT_fields = q.Sql("DESCRIBE " & s).ToDataTable
                For Each dr In DT_fields.Rows
                    dr("field") = StrConv(dr("field"), VbStrConv.ProperCase)
                Next

                s = StrConv(s, VbStrConv.ProperCase)


                For Each dr In DT_fields.Rows

                    If dr("Key") <> "" Then

                        type = to_mysql(dr("Type").ToString)
                        type_dotnet = to_dotnet(type)

                        SClass.WriteLine(
                            "Public Function By_" & s & "_" & dr("field") & "(v As " & type_dotnet & ") As BuildQuery(Of Tresult)" & vbCrLf &
                                    "Return Me.Where(""" & s & "." & dr("field") & """, ""="", v)" & vbCrLf &
                            "End Function"
                        )

                    End If
                Next



            End Sub


            Private Sub table_field(s As String)


                Dim q = New SqlQuery(Of Object)(Me.ConnStr)


                Dim DT_fields As New DataTable

                Dim type As String = 0
                Dim type_dotnet As String = 0
                Dim null As String = ""
                Dim auto_gen As String = ""
                Dim key As String = ""
                Dim dbtype As String = ""

                DT_fields = q.Sql("DESCRIBE " & s).ToDataTable
                For Each dr In DT_fields.Rows
                    dr("field") = StrConv(dr("field"), VbStrConv.ProperCase)
                Next

                s = StrConv(s, VbStrConv.ProperCase)


                'SClass.WriteLine(
                '    "<Table(Name:=""" & s & """)> _" & vbCrLf)

                SClass.WriteLine(
                    "Partial Public Class " & s & vbCrLf &
                    "Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged" & vbCrLf &
                    "Private Shared emptyChangingEventArgs As System.ComponentModel.PropertyChangingEventArgs = New System.ComponentModel.PropertyChangingEventArgs("""") " & vbCrLf
                )


                'SClass.WriteLine(
                '    "Partial Public Class helper" & vbCrLf &
                '        "Partial Public Class tab" & vbCrLf &
                '            "Private db As " & Me.ClassNameDB & "" & vbCrLf &
                '            "Public Sub New(db As " & Me.ClassNameDB & ")" & vbCrLf &
                '                "Me.db = db" & vbCrLf &
                '            "End Sub" & vbCrLf &
                '        "End Class" & vbCrLf &
                '    "End Class" & vbCrLf
                ')

                'SClass.WriteLine(
                '    GetProperty("TAB", "helper.tab", True, True, "(Me.db)") & vbCrLf
                ')




                SClass.WriteLine(
                    "Private ReadOnly Property DB() As " & ClassNameDB & "" & vbCrLf &
                        "Get" & vbCrLf &
                            "If Me.PropertyChangedEvent IsNot Nothing Then" & vbCrLf &
                                "Return Me.PropertyChangedEvent.Target.db" & vbCrLf &
                            "Else" & vbCrLf &
                                "Return Nothing" & vbCrLf &
                            "End If" & vbCrLf &
                        "End Get" & vbCrLf &
                    "End Property"
                )


                SClass.WriteLine(
                    "Public Function Clone() As " & s & vbCrLf &
                        "Dim entity As " & s & " = Me.MemberwiseClone()" & vbCrLf &
                        "RemoveHandler entity.PropertyChanged, Me.PropertyChangedEvent" & vbCrLf &
                        "Return entity" & vbCrLf &
                    "End Function " & vbCrLf
                )

                For Each dr In DT_fields.Rows

                    type = to_mysql(dr("Type").ToString)
                    type_dotnet = to_dotnet(type)
                    If type_dotnet = "string" Then
                        type_dotnet &= " = """""
                    End If

                    SClass.WriteLine("Private _" & dr("field") & " As " & type_dotnet)
                    'SClass.WriteLine("Public Shared Property " & dr("field") & "_field As String = """ & s & "." & dr("field") & """")
                    'Public Shared Property ElaborationID_field As String = ""

                Next

                SClass.WriteLine("")


                SClass.WriteLine(
                    "#Region ""Extensibility Method Declarations""" & vbCrLf &
                    "Partial Private Sub OnCreated()" & vbCrLf &
                    "End Sub" & vbCrLf
                )

                For Each dr In DT_fields.Rows

                    type = to_mysql(dr("Type").ToString)
                    type_dotnet = to_dotnet(type)

                    SClass.WriteLine("Partial Private Sub On" & dr("field") & "Changed()" & vbCrLf &
                                                    "End Sub" & vbCrLf)

                    SClass.WriteLine("Partial Private Sub On" & dr("field") & "Changing(ByVal value As " & type_dotnet & ")" & vbCrLf &
                                                    "End Sub" & vbCrLf)

                Next

                SClass.WriteLine("#End Region" & vbCrLf &
                                                "Public Sub New()" & vbCrLf &
                                                "MyBase.New()" & vbCrLf &
                                                "Me.OnCreated()" & vbCrLf &
                                                "End Sub" & vbCrLf)

                For Each dr In DT_fields.Rows

                    null = ""
                    auto_gen = ""
                    key = ""
                    dbtype = ""

                    type = to_mysql(dr("Type").ToString)
                    type_dotnet = to_dotnet(type)

                    If dr("Null").ToString = "NO" Then
                        null = ", CanBeNull:=False"
                    End If

                    If dr("Extra").ToString = "auto_increment" Then
                        auto_gen = " IsDbGenerated:=True,"
                    End If

                    If dr("Key").ToString = "PRI" Then
                        key = " IsPrimaryKey:=True,"
                    End If

                    If dr("Type").ToString = "int(10) unsigned zerofill" Then
                        dbtype = " DbType:=""int unsigned zerofill"","
                    End If


                    'SClass.WriteLine("<Column(Storage:=""_" & dr("field") & """, Name:=""" & dr("field") & """, DbType:=""" &
                    '                                    type & """, " & dbtype & key & auto_gen & " AutoSync:=AutoSync.Never" & null & "), _" &
                    '                                    vbCrLf & "DebuggerNonUserCode()> _" & vbCrLf)
                    Dim ifnothing As String = ""
                    If type_dotnet = "string" Then
                        ifnothing = " Or _" & dr("field") & " Is Nothing "
                    End If

                    SClass.WriteLine("Public Property " & dr("field") & "() As " & type_dotnet & vbCrLf &
                                                        "Get" & vbCrLf &
                                                        "Return Me._" & dr("field") & vbCrLf &
                                                        "End Get" & vbCrLf &
                                                        "Set(value As " & type_dotnet & ")" & vbCrLf &
                                                        "If (_" & dr("field") & " <> value " & ifnothing & " ) Then" & vbCrLf &
                                                        "Me.On" & dr("field") & "Changing(value)" & vbCrLf &
                                                        "Me.SendPropertyChanging()" & vbCrLf &
                                                        "Me._" & dr("field") & " = value" & vbCrLf &
                                                        "Me.SendPropertyChanged(""" & dr("field") & """)" & vbCrLf &
                                                        "Me.On" & dr("field") & "Changed()" & vbCrLf &
                                                        "End If" & vbCrLf &
                                                        "End Set" & vbCrLf &
                                                        "End Property" & vbCrLf)

                Next

                SClass.WriteLine("Public Event PropertyChanging As System.ComponentModel.PropertyChangingEventHandler" &
                    " Implements System.ComponentModel.INotifyPropertyChanging.PropertyChanging" & vbCrLf &
                    "Public Event PropertyChanged As System.ComponentModel.PropertyChangedEventHandler" &
                    " Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged" & vbCrLf &
                    "Protected Overridable Sub SendPropertyChanging()" & vbCrLf &
                    "Dim h As System.ComponentModel.PropertyChangingEventHandler = Me.PropertyChangingEvent" & vbCrLf &
                    "If (Not (h) Is Nothing) Then" & vbCrLf &
                    "h(Me, emptyChangingEventArgs)" & vbCrLf &
                    "End If" & vbCrLf &
                    "End Sub" & vbCrLf &
                    "Protected Overridable Sub SendPropertyChanged(ByVal propertyName As String)" & vbCrLf &
                    "Dim h As System.ComponentModel.PropertyChangedEventHandler = Me.PropertyChangedEvent" & vbCrLf &
                    "If (Not (h) Is Nothing) Then" & vbCrLf &
                    "h(Me, New System.ComponentModel.PropertyChangedEventArgs(propertyName))" & vbCrLf &
                    "End If" & vbCrLf &
                    "End Sub" & vbCrLf &
                    "End Class" & vbCrLf)


                'SClass.WriteLine("Partial Public Class fn" & s)
                'For Each dr In DT_fields.Rows
                '    SClass.WriteLine("Public Property " & dr("field") & " As String = """ & dr("field") & """")
                'Next
                'SClass.WriteLine("End Class")

                'SClass.WriteLine("Partial Public Class ftn" & s)
                'For Each dr In DT_fields.Rows
                '    SClass.WriteLine("Public Property " & dr("field") & " As String = """ & s & "." & dr("field") & """")
                'Next
                'SClass.WriteLine("End Class")


            End Sub

            Private Sub table_struct(s As String)

                Dim q = New SqlQuery(Of Object)(Me.ConnStr)


                Dim DT_fields As New DataTable

                Dim type As String = 0
                Dim type_dotnet As String = 0
                Dim null As String = ""
                Dim auto_gen As String = ""
                Dim key As String = ""
                Dim dbtype As String = ""

                DT_fields = q.Sql("DESCRIBE " & s).ToDataTable
                For Each dr In DT_fields.Rows
                    dr("field") = StrConv(dr("field"), VbStrConv.ProperCase)
                Next

                s = StrConv(s, VbStrConv.ProperCase)

                SClass.WriteLine("newtab = New DBContext.TableProperty")

                For Each dr In DT_fields.Rows
                    null = ""
                    auto_gen = "False"
                    key = "False"
                    dbtype = ""

                    If dr("Extra").ToString = "auto_increment" Then
                        auto_gen = "True"
                        SClass.WriteLine("newtab.DBGeneratedField = """ & dr("field") & """")
                    End If

                    If dr("Key").ToString = "PRI" Then
                        key = "True"
                        SClass.WriteLine("newtab.PrimaryKeys.Add(""" & dr("field") & """)")
                    End If

                    SClass.WriteLine("newtab.Fields.Add(""" & dr("field") & """, New DBContext.FieldProperty With {.IsDbGenerated = " & auto_gen & ", .IsPrimaryKey = " & key & "})")

                Next

                SClass.WriteLine("Me.Add(""" & s & """, newtab)")



            End Sub


            Private Function to_mysql(s As String) As String

                Dim Val = s.IndexOfAny({"("c, " "c})

                If Val = -1 Then

                    Return s

                Else

                    Dim tmp = s.Substring(0, Val)

                    If s.IndexOf("unsigned") <> -1 Then

                        Return "u" & tmp
                    End If

                    Return tmp

                End If

                Return False

            End Function
            Private Function to_dotnet(s As String) As String


                Select Case s.ToLower
                    Case "double"
                        Return "double"

                    Case "varchar"
                        Return "string"

                    Case "char"
                        Return "string"

                    Case "uint"
                        Return "uinteger"

                    Case "int"
                        Return "integer"

                    Case "smallint"
                        Return "short"

                    Case "usmallint"
                        Return "ushort"

                    Case "text", "mediumtext", "longtext"
                        Return "string"

                    Case "utinyint"
                        Return "byte"

                    Case "tinyint"
                        Return "sbyte"

                    Case "float"
                        Return "single"

                    Case "bigint"
                        Return "long"

                    Case "ubigint"
                        Return "Ulong"

                    Case "bit"
                        Return "Boolean"

                    Case "timestamp", "datetime"
                        Return "Date"


                    Case Else
                        Return "---------"

                End Select

                Return False

            End Function

        End Class
    End Namespace

End Namespace

